Auto increment in Oracle SQL

0
votes

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"...

2
sqloracleoracle11g
why did you tag this sql server if it is about oracle. – Hogan
Stackoverflow suggested it! – yooouuri
It suggested it as a tag that starts with the letters sql? – Hogan
I would suggest writing your insert statements with explicit column names too, so you can tell which value maps to which column. – Marc

2 Answers

6
votes

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
0
votes

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');