This question came to me over Twitter, so I thought I’d whip out a quick post on it
Yes, we do implement the IDENTITY column via a sequence. (Digression – I think this is a smart thing to do. After all, we’ve had sequences for 20 years, so we know how they work, they are mature, tested, and rock solid, so why invent something new?)
So Peter’s question was – why not just do what we’ve always done and create a sequence and assign it as the default. And yes, there is nothing wrong with doing that – it will work just fine. But there are a couple of subtle differences between that and using the IDENTITY syntax.
1) The sequence is bound to the table, so when I drop the table, the sequence disappears as well
SQL> create table t ( x int generated as identity); Table created. SQL> select object_name from user_objects where created > sysdate - 60/86400; OBJECT_NAME ---------------------------------------- T ISEQ$$_195276 SQL> drop table t purge; Table dropped. SQL> select object_name from user_objects where created > sysdate - 60/86400; no rows selected
2) You can lock down the column
SQL> create table t ( x int generated always as identity); Table created. SQL> insert into t values ( 12 ) ; insert into t values ( 12 ) * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column
Personally, I don’t have a preference. The most important thing to me was that sequences can be used as a default value to avoid triggers on every single table.
Is it possible to have two generate columns in one table? One as the artificial primary key and the other as a sequence, part of an alternate key? Or do I need to use a sequence?
Thanks in advance,