autoincrementing columns

September 27, 2013 — Leave a comment

postgresql provides it, msssql (I think) provides it, too. and finally, with release 12c, you may have auto incrementing columns in oracle:

SQL> create table t1 ( a number generated always as identity, b number );
SQL> insert into t1 (b) values (1);

1 row created.

SQL> insert into t1 (b) values (2);

1 row created.

SQL> select * from t1;

	 A	    B
---------- ----------
	 1	    1
	 2	    2

what happens in the background is quite simple: oracle creates a sequence:

SQL> select sequence_name from user_sequences;

SEQUENCE_NAME
--------------------------------------------------------------------------------
ISEQ$$_19540

user/dba/all_tables has a new column to refect this:

SQL> select HAS_IDENTITY from user_tables where table_name = 'T1';

HAS
---
YES

but it is not possible to have more than one of these:

SQL> create table t2 ( a number generated always as identity, b number generated always as identity );
create table t2 ( a number generated always as identity, b number generated always as identity )
                                                                                               *
ERROR at line 1:
ORA-30669: table can have only one identity column

once the table is dropped, the sequence is gone, too:

SQL> drop table t1;

Table dropped.

SQL> select sequence_name from user_sequences;

no rows selected

SQL> 
Advertisements

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s