Oracle 12c auto-incrementing column Identity Columns

Oracle 12c auto-incrementing column Identity Columns

In the 12c version of Oracle, Oracle implements auto-increment columns similar to auto_increment in MySQL. Let's take a look at how Oracle is implemented.

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table t (userid number GENERATED ALWAYS AS IDENTITY, uname varchar2 (200 ));

Table created.

SQL> select * from t;

No rows selected

SQL> insert into t valuse ('aaa ');
Insert into t valuse ('aaa ')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> insert into t values (1, 'aaa ');
Insert into t values (1, 'aaa ')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

SQL> insert into t values ('aaa ');
Insert into t values ('aaa ')
*
ERROR at line 1:
ORA-00947: not enough values

SQL> insert into t (uname) values ('aaa ');

1 row created.

SQL> insert into t (uname) values ('bbb ');

1 row created.

SQL> insert into t (uname) values ('ccc ');

1 row created.

SQL> select * from t;
USERID UNAME
----------------------------------------
1 aaa
2 bbb
3 ccc
You can view the table DDL using the metadata Package.
SQL> select dbms_metadata.get_ddl ('table', 'T') FROM DUAL;

Create table "SYS". "T"
("USERID" number generated always as identity minvalue 1 MAXVALUE 9999999
999999999999999999999 increment by 1 start with 1 CA
CHE 20 noorder nocycle not null enable,
"UNAME" VARCHAR2 (200)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 214748364
5
PCTINCREASE 0 FREELISTS 1 freelist groups 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC
He default)
TABLESPACE "SYSTEM"

Taking a closer look, this auto-incrementing column is the sequence syntax, which is actually a sequence internally.