Oracle 12c学习系列之—identity column
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: Oracle 12c学习系列之—identity column
在12c之前，如果想实现column的自动增长，通常是通过sequence来实现，然而这个问题在12c中得到了解决，Oracle推迟了identity column功能，该功能完美的解决了之前的问题，下面是我的简单测试，供参考！
|
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 36 37 38 39 40 41 42 43 44 45 46 47 |
SQL> alter pluggable database all open; Pluggable database altered. SQL> select name,open_mode from V$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBORCL READ WRITE SQL> conn roger/roger@pdborcl Connected. SQL> show con_name CON_NAME ------------------------------ PDBORCL SQL> create table test(id number generated by default as identity , 2 name varchar2(20)); Table created. SQL> insert into test(name) values('roger'); 1 row created. SQL> insert into test(name) values('killdb.com'); 1 row created. SQL> commit; Commit complete. SQL> select * from test; ID NAME ---------- -------------------- 1 roger 2 killdb.com SQL> SQL> insert into test(id,name) values(null,'killdb.com'); insert into test(id,name) values(null,'killdb.com') * ERROR at line 1: ORA-01400: cannot insert NULL into ("ROGER"."TEST"."ID") |
可以看到id列默认可以进行自动增长，在12c之前，Oracle只能通过sequence来实现这个功能。 另外我们还可以看到，这种情况下，是不能插入null值的。
|
1 2 3 4 5 |
SQL> alter table test modify (id default null); alter table test modify (id default null) * ERROR at line 1: ORA-30674: identity column cannot have a default value |
那么对于identity 的column，真的不能插入null值吗？ 其实是可以的，不过你得这样做：
|
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 |
<pre class="brush:php">SQL> create table test1 (id number generated by default on NULL as identity ,name varchar2(20)); Table created. SQL> SQL> insert into test1 values(1,'killdb.com'); 1 row created. SQL> insert into test1 values(2,'baidu.com'); 1 row created. SQL> insert into test1 values(null,'google.com'); 1 row created. SQL> commit; Commit complete. SQL> select * from test1; ID NAME ---------- -------------------- 1 killdb.com 2 baidu.com 1 google.com |
不过比较奇怪的是，大家看到了，插入的第3条数据的id列为null的情况下，oracle自己实际的值为1. 这个1是怎么来的呢 ？后面会告诉你答案。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> update test1 set id=100 where id=2; 1 row updated. SQL> commit; Commit complete. SQL> select * from test1; ID NAME ---------- -------------------- 1 killdb.com 100 baidu.com 1 google.com |
这种情况下，可以进行正常的dml，因为identity column默认是为null的，下面继续一个测试。
|
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 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
SQL> create table test2 (id number generated always as identity ,name varchar2(20)); Table created. SQL> insert into test2(name) values('killdb.com'); 1 row created. SQL> insert into test2(name) values('baidu.com'); 1 row created. SQL> insert into test2(name) values('google.com'); 1 row created. SQL> commit; Commit complete. SQL> select * from test2; ID NAME ---------- -------------------- 1 killdb.com 2 baidu.com 3 google.com SQL> update test2 set id=4 where id=2; update test2 set id=4 where id=2 * ERROR at line 1: ORA-32796: cannot update a generated always identity column SQL> update test2 set id=1 where id=2; update test2 set id=1 where id=2 * ERROR at line 1: ORA-32796: cannot update a generated always identity column SQL> delete from test2 where id=2; 1 row deleted. SQL> commit; Commit complete. SQL> select * from test2; ID NAME ---------- -------------------- 1 killdb.com 3 google.com |
我们可以看到，居然不能进行update，这里想想也应该知道，Oracle是不会允许你进行update的。
|
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 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
SQL> SQL> select TABLE_NAME, 2 COLUMN_NAME, 3 DEFAULT_ON_NULL, 4 IDENTITY_COLUMN, 5 DATA_DEFAULT 6 from user_tab_columns 7 where IDENTITY_COLUMN='YES'; TABLE_NAME COLUMN_NAME DEF IDE DATA_DEFAULT -------------------- -------------------- --- --- ------------------------------------------------------------ TEST ID NO YES "ROGER"."ISEQ$$_91820".nextval TEST1 ID YES YES "ROGER"."ISEQ$$_91822".nextval TEST2 ID NO YES "ROGER"."ISEQ$$_91824".nextval SQL> SQL> set pagesize 200 long 9999 SQL> select dbms_metadata.get_ddl('TABLE','TEST') from dual; DBMS_METADATA.GET_DDL('TABLE','TEST') -------------------------------------------------------------------------------- CREATE TABLE "ROGER"."TEST" ( "ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999 999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOT NULL ENABLE, "NAME" VARCHAR2(20) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" SQL> select dbms_metadata.get_ddl('TABLE','TEST1') from dual; DBMS_METADATA.GET_DDL('TABLE','TEST1') -------------------------------------------------------------------------------- CREATE TABLE "ROGER"."TEST1" ( "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 99 99999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOT NULL ENABLE, "NAME" VARCHAR2(20) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" |
最后通过表的定义，我们可以看到，ID列默认被定义为了sequence，从属性来看跟我们之前版本中创建sequence的属性差不多。而且我们看到默认情况下start with 为1。这个也就是为什么前面插入null的情况下id=1的原因。
Leave a Reply
You must be logged in to post a comment.