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.