Finally! I can count how many times I’ve seen questions like this one.
The new Oracle 12c now allows to define a table with the sequence.nextval directly in the in-line column definition:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
SQL> create sequence foo_seq; Sequence created. SQL> create table foo ( 2 id number default foo_seq.nextval, 3 bar varchar2(50) not null, 4 constraint foo_pk primary key (id) 5 ); Table created. SQL> insert into foo (bar) values ('baz'); 1 row created. SQL> insert into foo (bar) values ('test'); 1 row created. SQL> insert into foo (bar) values ('whoo'); 1 row created. SQL> commit; Commit complete. SQL> select * from foo; ID BAR ---------- -------------------------------------------------- 1 baz 2 test 3 whoo |
But Oracle has fixed this twice, in the new release it’s possible to use identity columns as well, avoiding the necessity to create explicitly a new sequence:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SQL> create table bar ( 2 id number generated as identity, 3 foo varchar2(50) not null, 4 constraint bar_pk primary key (id) 5 ); Table created. SQL> insert into bar (foo) values ('baz'); 1 row created. SQL> insert into bar (foo) values ('test'); 1 row created. SQL> insert into bar (foo) values ('whoo'); 1 row created. SQL> commit; Commit complete. SQL> select * from bar; ID FOO ---------- -------------------------------------------------- 1 baz 2 test 3 whoo |
I’ve said “explicitly” because actually a sequence is created with a system-generated name, so you’ll still need to deal with sequences.
|
1 2 3 |
TABLE_NAME TABLE_TYPE ------------------------------- ----------- ISEQ$$_23657 SEQUENCE |
cheers
Ludo
The following two tabs change content below.
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
Pingback: Oracle Database 12c (12.1) Installation and New Features | DBLinks Consulting LTDDBLinks Consulting LTD
Pingback: Oracle Database 12c (12.1) Installation and New Features | DBLinks Consulting Ltée