Auto increment in Oracle SQL

Question

I am trying create a table with auto increment in Oracle SQL. I have the following SQL code:

DROP TABLE "Account" CASCADE CONSTRAINTS;
CREATE TABLE "Account" (
  "Id" NUMBER(5) NOT NULL,
  "Name" VARCHAR2(32) NOT NULL,
  "User" VARCHAR2(16) NOT NULL,
  "Email" VARCHAR2(32) NOT NULL,
  "Password" VARCHAR2(16) NOT NULL,
  "Level" VARCHAR2(16) NOT NULL,
  CONSTRAINT "Account_pk" PRIMARY KEY ("Id")
);

DROP SEQUENCE "Account_seq";
CREATE SEQUENCE "Account_seq" START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000;

INSERT INTO "Account" VALUES (Account_seq.NEXTVAL, 'xxx', 'xxx', 'xxx', 'xxx', 'user');
INSERT INTO "Account" VALUES (Account_seq.NEXTVAL, 'xxx', 'xxx', 'xxx', 'xxx', 'user');

Oracle SQL Developer says that Account_seq is created, but still it gives me an error: "sequence does not exist"...

I would suggest writing your insert statements with explicit column names too, so you can tell which value maps to which column. — Marc
– Marc, Commented Oct 2, 2015 at 15:48

sstan · Accepted Answer · 2015-10-02 15:57:12Z

6

If you use double quotes to create an object (and the object name is not all upper case), then you are forced to use double quotes everywhere you refer to it.

So in this case, this would work (notice the double quotes around Account_seq):

INSERT INTO "Account" VALUES ("Account_seq".NEXTVAL, 'xxx', 'xxx', 'xxx', 'xxx', 'user');

That said, using double quotes everywhere is annoying. Instead, consider changing the sequence creation to avoid using double quotes:

CREATE SEQUENCE Account_seq ...

... that way, you can refer to it without double quotes and the sequence name won't be case sensitive.

EDIT

To clarify the case sensitivity part a little bit:

When you double quote an identifier, then Oracle looks for that identifier with that exact casing.

On the other hand, if you don't use double quotes, it will look for the equivalent identifier all upper-cased.

Example:

"Account_seq": it will look for Account_seq
"AcCoUnT_SeQ": it will look for AcCoUnT_SeQ
Account_seq: it will look for ACCOUNT_SEQ
AcCoUnT_SeQ: it will look for ACCOUNT_SEQ

edited Oct 2, 2015 at 15:57

answered Oct 2, 2015 at 15:45

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marc Over a year ago

Yup. And in my opinion, there's not usually a good reason to quote the strings like that.

Gordon Linoff Over a year ago

Case sensitivity isn't the issue, because the case is the same.

Hogan Over a year ago

@GordonLinoff - are you sure that is the case?

Gordon Linoff Over a year ago

@Hogan . . . I'm reading the question and "Account_seq" and Account_seq have the same case a I read them.

Alex · Accepted Answer · 2015-10-02 15:48:38Z

0

If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object. So your insert statements should be:

INSERT INTO "Account" VALUES ("Account_seq".NEXTVAL, 'xxx', 'xxx', 'xxx', 'xxx', 'user');
INSERT INTO "Account" VALUES ("Account_seq".NEXTVAL, 'xxx', 'xxx', 'xxx', 'xxx', 'user');

answered Oct 2, 2015 at 15:48

Alex

21.9k11 gold badges68 silver badges77 bronze badges

Collectives™ on Stack Overflow

Auto increment in Oracle SQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related