I had created a new table named USERLOG with two fields from a previous VIEW. The table already consist of about 9000 records. The two fields taken from the VIEW, i.e. weblog_views consist of IP (consists of IP address), and WEB_LINK (consists of URL). This is the code I used,
我创建了一个名为USERLOG的新表,其中包含以前视图中的两个字段。该表已包含约9000条记录。从视图获取的两个字段,即weblog_views由IP(包含IP地址)和WEB_LINK(包含URL)组成。这是我用过的代码,
CREATE TABLE USERLOG
AS
SELECT C_IP, WEB_LINK FROM weblog_views;
I want to add another column to this table called the USER_ID, which would consists of a sequence starting with 1 to 9000 records to create a unique id for each existing rows. I need help with this part. I'm using Oracle SQL Developer: ODMiner version 3.0.04. I tried using the AUTO-INCREMENT option,
我想向这个名为USER_ID的表中添加另一列,该列由一个序列组成,从1到9000条记录开始,为每个现有的行创建唯一的id。这部分我需要帮助。我使用Oracle SQL Developer: ODMiner版本3.0.04。我尝试使用自动递增选项,
ALTER TABLE USERLOG
ADD USER_ID INT UNSIGNED NOT NULL AUTO_INCREMENT;
But I get an error with this,
但是我得到了一个错误,
Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 - "invalid ALTER TABLE option"
So, I would really appreciate any help that I can get!
所以,我真的很感激我能得到的任何帮助!
2 个解决方案
#1
38
You would need to add a column
您需要添加一个列
ALTER TABLE userlog
ADD( user_id number );
create a sequence
创建一个序列
CREATE SEQUENCE user_id_seq
START WITH 1
INCREMENT BY 1
CACHE 20;
Update the data in the table
更新表中的数据。
UPDATE userlog
SET user_id = user_id_seq.nextval
Assuming that you want user_id
to be the primary key, you would then add the primary key constraint
假设您希望user_id是主键,那么您将添加主键约束
ALTER TABLE userlog
ADD CONSTRAINT pk_user_id PRIMARY KEY( user_id );
If you want to use the sequence to automatically add the user_id
when you do an INSERT
(the other option would be to specifically reference user_id_seq.nextval
in your INSERT
statements, you would also need a trigger
如果您希望使用序列在进行插入时自动添加user_id(另一个选项是专门引用user_id_seq)。在INSERT语句中,还需要一个触发器
CREATE OR REPLACE TRIGGER trg_userlog_user_id
BEFORE INSERT ON userlog
FOR EACH ROW
BEGIN
:new.user_id := user_id_seq.nextval;
END;
#2
0
In addition to Justin's excellent answer you might want to prevent NULL values for your user_id in the future (as they could still be caused by UPDATE statements). Therefore, execute the following statement at the end:
除了Justin的出色回答之外,您可能希望在将来防止user_id的空值(因为它们仍然可能是由UPDATE语句引起的)。因此,在最后执行以下语句:
ALTER TABLE userlog MODIFY(user_id number NOT NULL);
修改表userlog修改(user_id号不为空);
#1
38
You would need to add a column
您需要添加一个列
ALTER TABLE userlog
ADD( user_id number );
create a sequence
创建一个序列
CREATE SEQUENCE user_id_seq
START WITH 1
INCREMENT BY 1
CACHE 20;
Update the data in the table
更新表中的数据。
UPDATE userlog
SET user_id = user_id_seq.nextval
Assuming that you want user_id
to be the primary key, you would then add the primary key constraint
假设您希望user_id是主键,那么您将添加主键约束
ALTER TABLE userlog
ADD CONSTRAINT pk_user_id PRIMARY KEY( user_id );
If you want to use the sequence to automatically add the user_id
when you do an INSERT
(the other option would be to specifically reference user_id_seq.nextval
in your INSERT
statements, you would also need a trigger
如果您希望使用序列在进行插入时自动添加user_id(另一个选项是专门引用user_id_seq)。在INSERT语句中,还需要一个触发器
CREATE OR REPLACE TRIGGER trg_userlog_user_id
BEFORE INSERT ON userlog
FOR EACH ROW
BEGIN
:new.user_id := user_id_seq.nextval;
END;
#2
0
In addition to Justin's excellent answer you might want to prevent NULL values for your user_id in the future (as they could still be caused by UPDATE statements). Therefore, execute the following statement at the end:
除了Justin的出色回答之外,您可能希望在将来防止user_id的空值(因为它们仍然可能是由UPDATE语句引起的)。因此,在最后执行以下语句:
ALTER TABLE userlog MODIFY(user_id number NOT NULL);
修改表userlog修改(user_id号不为空);