如何将序列列添加到具有记录的现有表中 [英] How to add a sequence column to an existing table with records

问题描述

我创建了一个名为USERLOG的新表，其中包含来自上一个VIEW的两个字段.该表已经包含大约9000条记录.从VIEW获取的两个字段，即weblog_views由IP(由IP地址组成)和WEB_LINK(由URL组成)组成.这是我使用的代码，

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,

    CREATE TABLE USERLOG
    AS
    SELECT C_IP, WEB_LINK FROM weblog_views;

我想在此表中添加另一列称为USER_ID，该列将包含一个从1到9000条记录开始的序列，以为每个现有行创建唯一的ID.我需要这部分的帮助.我正在使用Oracle SQL Developer:ODMiner版本3.0.04. 我尝试使用AUTO-INCREMENT选项，

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,

    ALTER TABLE USERLOG
    ADD USER_ID INT UNSIGNED NOT NULL AUTO_INCREMENT;

但我对此有误，

    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!

推荐答案

您需要添加一列

ALTER TABLE userlog
  ADD( user_id number );

创建序列

CREATE SEQUENCE user_id_seq
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

更新表中的数据

UPDATE userlog
   SET user_id = user_id_seq.nextval

假设您希望user_id作为主键，则需要添加主键约束

Assuming that you want user_id to be the primary key, you would then add the primary key constraint

ALTER TABLE userlog
  ADD CONSTRAINT pk_user_id PRIMARY KEY( user_id );

如果要在执行INSERT时使用序列自动添加user_id(另一种选择是在INSERT语句中专门引用user_id_seq.nextval，则还需要触发器

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

CREATE OR REPLACE TRIGGER trg_userlog_user_id
  BEFORE INSERT ON userlog
  FOR EACH ROW
BEGIN
  :new.user_id := user_id_seq.nextval;
END;

这篇关于如何将序列列添加到具有记录的现有表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！