Oracle - 修改现有表以自动增加一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7681122/
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
Oracle - Modify an existing table to auto-increment a column
提问by Mike Christensen
I have a table with the following column:
我有一个包含以下列的表格：
NOTEID NUMBER NOT NULL,
For all intents and purposes, this column is the primary key. This table has a few thousand rows, each with a unique ID. Before, the application would SELECT the MAX() value from the table, add one, then use that as the next value. This is a horrible solution, and is not transaction or thread safe (in fact, before they didn't even have a UNIQUE constraint on the column and I could see the same NOTEID was duplicated in 9 different occasions)..
出于所有意图和目的，此列是主键。这个表有几千行，每行都有一个唯一的 ID。之前，应用程序将从表中选择 MAX() 值，添加一个，然后将其用作下一个值。这是一个可怕的解决方案，并且不是事务或线程安全的（事实上，在他们甚至在列上没有 UNIQUE 约束之前，我可以看到相同的 NOTEID 在 9 个不同的场合重复）。
I'm rather new to Oracle, so I'd like to know the best syntax to ALTER this table and make this column auto-increment instead. If possible, I'd like to make the next value in the sequence be the MAX(NOTEID) + 1 in the table, or just make it 800 or something to start out. Thanks!
我对 Oracle 比较陌生，所以我想知道最好的语法来改变这个表并使这个列自动增加。如果可能，我想将序列中的下一个值设为表中的 MAX(NOTEID) + 1，或者只是设为 800 或其他值。谢谢！
回答by Justin Cave
You can't alter the table. Oracle doesn't support declarative auto-incrementing columns. You can create a sequence
你不能改变桌子。Oracle 不支持声明式自动递增列。您可以创建一个序列
CREATE SEQUENCE note_seq
START WITH 800
INCREMENT BY 1
CACHE 100;
Then, you can create a trigger
然后，您可以创建触发器
CREATE OR REPLACE TRIGGER populate_note_id
BEFORE INSERT ON note
FOR EACH ROW
BEGIN
:new.note_id := note_seq.nextval;
END;
or, if you want to allow callers to specify a non-default NOTE_ID
或者，如果您想允许调用者指定一个非默认值 NOTE_ID
CREATE OR REPLACE TRIGGER populate_note_id
BEFORE INSERT ON note
FOR EACH ROW
BEGIN
IF( :new.note_id is null )
THEN
:new.note_id := note_seq.nextval;
END IF;
END;
回答by roartechs
If your MAX(noteid) is 799, then try:
如果您的 MAX(noteid) 是 799，请尝试：
CREATE SEQUENCE noteseq
START WITH 800
INCREMENT BY 1
Then when inserting a new record, for the NOTEID column, you would do:
然后在插入新记录时，对于 NOTEID 列，您将执行以下操作：
noteseq.nextval
