oracle - 如何使用序列作为自动增量 ID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13799005/
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 - how to use sequence as auto increment id?
提问by Rock
I have an oracle database table:
我有一个 oracle 数据库表：
CREATE TABLE "DogInfo" (
"Id" NUMBER NOT NULL ENABLE,
"DogName" VARCHAR2 (50 CHAR) NOT NULL ENABLE,
"DogAge" NUMBER NOT NULL ENABLE,
CONSTRAINT "DogInfo_PK" PRIMARY KEY ("Id") ENABLE
);
CREATE SEQUENCE "DOGINFO_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;
CREATE OR REPLACE TRIGGER "BI_DogInfo"
BEFORE INSERT ON "DogInfo" FOR EACH ROW
WHEN (NEW."Id" IS NULL OR NEW."Id" = 0) BEGIN
SELECT "USERINFO_SEQ".nextval INTO :NEW."Id" FROM dual;
END;
ALTER TRIGGER "BI_DogInfo" ENABLE;
If I insert 20 records into the table with database tool, and then use my C# web application to insert records, the dog id will start with 1, rather than 21.
如果我用数据库工具在表中插入 20 条记录，然后使用我的 C# web 应用程序插入记录，狗 id 将从 1 开始，而不是 21。
Anyone can help me fix this bug?
谁能帮我修复这个错误？
Thanks.
谢谢。
回答by Grzegorz W
Sequence is not an "auto increment id".
序列不是“自动递增 ID”。
Sequence is just a sequential unique number generator. It can work as your Id column value provider but it is up tu You to keep proper values in the column.
Sequence 只是一个序列唯一编号生成器。它可以作为您的 Id 列值提供者，但您需要在列中保留正确的值。
I assume that You add your 20 rows like this:
我假设您像这样添加了 20 行：
insert into table(id, <columns>) values (1, <values>);
insert into table(id, <columns>) values (2, <values>);
and so on ...
Your sequence has no way of knowing what is the next number you "expect" it to have (unless of course you (re)create it with desired initial value). Instead You should always use values from your sequence like this:
您的序列无法知道您“期望”它拥有的下一个数字是什么（除非您（当然）使用所需的初始值（重新）创建它）。相反，您应该始终使用序列中的值，如下所示：
insert into table(id, <columns>) values (sequence.nextval, <values>);
insert into table(id, <columns>) values (sequence.nextval, <values>);
and so on ...
This way you'll keep sequence in sync with table id values.
通过这种方式，您将保持序列与表 id 值同步。
EDIT :
编辑 ：
You can imitate that behaviour by using trigger and sequence as described in this answer.
您可以使用本答案中所述的触发器和序列来模仿该行为。
Also IDENTITY column is now available on Oracle 12c
此外，IDENTITY 列现在在 Oracle 12c 上可用
回答by Mupmup
You need to change your sequence if you want to start by 20 rather than 1.
如果你想从 20 而不是 1 开始，你需要改变你的顺序。
CREATE SEQUENCE "DOGINFO_SEQ" MINVALUE 20 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 20 CACHE 20 NOORDER NOCYCLE;
And it seems you didn't use the right sequence in your trigger.
似乎您没有在触发器中使用正确的序列。
CREATE OR REPLACE TRIGGER "BI_DogInfo"
BEFORE INSERT ON "DogInfo" FOR EACH ROW
WHEN (NEW."Id" IS NULL OR NEW."Id" = 0) BEGIN
SELECT "DOGINFO_SEQ".nextval INTO :NEW."Id" FROM dual;
END;
回答by posix
I think you using wrong Seq name.
我觉得你用错了Seq name。
Instead of using DOGINFO_SEQuse USERINFO_SEQ.
而不是使用DOGINFO_SEQuse USERINFO_SEQ。
