CODEWITHSUNDEEP
oracle-database
Rock
Rock

Reputation: 205

oracle - how to use sequence as auto increment id?

I have an oracle database table:

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.

Anyone can help me fix this bug?

Thanks.

Upvotes: 2

Views: 24229

Answers (3)

posix
posix

Reputation: 1

I think you using wrong Seq name.

Instead of using DOGINFO_SEQ use USERINFO_SEQ.

Upvotes: -1

Mupmup
Mupmup

Reputation: 93

You need to change your sequence if you want to start by 20 rather than 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;

Upvotes: 0

Grzegorz W
Grzegorz W

Reputation: 3517

Sequence is not an "auto increment 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.

I assume that You add your 20 rows like this:

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.

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

Upvotes: 7

Related Questions