SQL 如何将序列列添加到包含记录的现有表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13782824/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add a sequence column to an existing table with records
提问by user1888543
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 的新表，其中包含来自先前 VIEW 的两个字段。该表已经包含大约 9000 条记录。取自VIEW的两个字段，即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 版。我尝试使用 AUTO-INCREMENT 选项，
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!
所以，我真的很感激我能得到的任何帮助！
回答by Justin Cave
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_idto 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_idwhen you do an INSERT(the other option would be to specifically reference user_id_seq.nextvalin your INSERTstatements, you would also need a trigger
如果您想使用该序列user_id在您执行 an 时自动添加INSERT（另一种选择是user_id_seq.nextval在您的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;
回答by fuggi
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:
除了贾斯汀的出色答案之外，您可能希望将来防止 user_id 为 NULL 值（因为它们仍然可能由 UPDATE 语句引起）。因此，最后执行以下语句：
ALTER TABLE userlog MODIFY(user_id number NOT NULL);
ALTER TABLE userlog MODIFY(user_id number NOT NULL);
