Geek Answers Handbook

SQLPlus error AUTO_INCREMENT

When I try to run the following command in SQLPlus:

CREATE TABLE Hotel
(hotelNo NUMBER(4) NOT NULL AUTO_INCREMENT,
hotelName VARCHAR(20) NOT NULL,
city VARCHAR(50) NOT NULL,
CONSTRAINT hotelNo_pk PRIMARY KEY (hotelNo));

I get the following error:

(hotelNo NUMBER(4) NOT NULL AUTO_INCREMENT,
                        *
ERROR at line 2:
ORA-00907: missing right parenthesis

What am I doing wrong?

+5
source share
3 answers

Many will say that this is not a standard function in Oracle, but when it is as simple as two more commands after the CREATE TABLE command, I cannot find reasons to use SQL fancy for each insert. First, let's create a simple table for the game.

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));
Table created.

Now suppose we want the ID to be an auto-increment field. First we need a sequence to capture values ​​from.

SQL> CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;
Sequence created.

Now we can use this sequence in the BEFORE INSERT trigger in the table.

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/


SQL> INSERT INTO test (name) VALUES ('Jon');
1 row created.

SQL> INSERT INTO test (name) VALUES (’Bork’);
1 row created.

SQL> INSERT INTO test (name) VALUES (’Matt’);
1 row created.

SQL> SELECT * FROM test;

ID NAME
———- ——————————
1 Jon
2 Bork
3 Matt
+11

Oracle auto_increment, .

+2

, Oracle 12.1, :

CREATE TABLE employee 
(
    id NUMBER GENERATED by default on null as IDENTITY
    ....
)
+1

More articles:


All Articles