จำไว้ว่า Identity คอลัมน์คือการสร้าง Sequence ดังนั้น USER ต้องมีสิทธิ์ในการสร้าง Sequence นะครับ
ขอใช้ตัวอย่างจาก Oracle Document มานำเสนอนะครับ
SQL> grant create table to demo;ตัวอย่างอื่นๆ
Grant succeeded.
SQL> conn demo
Enter password:
Connected.
SQL> CREATE TABLE t1 (id NUMBER GENERATED AS IDENTITY, name varchar2(20));
CREATE TABLE t1 (id NUMBER GENERATED AS IDENTITY, name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> grant create sequence to demo;
Grant succeeded.
SQL> conn demo
Enter password:
Connected.
SQL> CREATE TABLE t1 (id NUMBER GENERATED AS IDENTITY, name varchar2(20));
Table created.
SQL> select * from USER_TAB_IDENTITY_COLS;
TABLE_NAME COLUMN_NAME GENERATION
------------------------------ ------------------------------ ----------
IDENTITY_OPTIONS
------------------------------------------------------------------------------------------------------------------------------------------------------
T1 ID ALWAYS
START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N
SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
------------------------------ ---------- ---------- ------------ - - ---------- ----------- --------------- - -
ISEQ$$_92172 1 1.0000E+28 1 N N 20 1 N N
SQL> select * from T1;
no rows selected
SQL> desc T1
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
SQL> insert into T1(name) values('Surachart Opun');
1 row created.
SQL> select * from T1;
ID NAME
---------- --------------------
1 Surachart Opun
SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
------------------------------ ---------- ---------- ------------ - - ---------- ----------- --------------- - -
ISEQ$$_92172 1 1.0000E+28 1 N N 20 21 N N
SQL> insert into T1(name) values('Test Again!');
1 row created.
SQL> select * from T1;
ID NAME
---------- --------------------
1 Surachart Opun
2 Test Again!
SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
------------------------------ ---------- ---------- ------------ - - ---------- ----------- --------------- - -
ISEQ$$_92172 1 1.0000E+28 1 N N 20 21 N N
SQL> CREATE TABLE t2 (id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10), Name varchar2(20));
Table created.
SQL> select * from USER_TAB_IDENTITY_COLS where table_name='T2';
TABLE_NAME COLUMN_NAME GENERATION
------------------------------ ------------------------------ ----------
IDENTITY_OPTIONS
------------------------------------------------------------------------------------------------------------------------------------------------------
T2 ID BY DEFAULT
START WITH: 100, INCREMENT BY: 10, MAX_VALUE: 9999999999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N
SQL> select * from t2;
no rows selected
SQL> insert into T2(name) values ('Chart');
1 row created.
SQL> select * from t2;
ID NAME
---------- --------------------
100 Chart
***************************************************************************
SQL> CREATE TABLE t3 (id NUMBER GENERATED BY DEFAULT AS IDENTITY , name varchar2(20));
Table created.
SQL> CREATE TABLE t4 (id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY , name varchar2(20));
Table created.
SQL>
SQL>
SQL> insert into T3 values(10,'TEST');
1 row created.
SQL> insert into T4 values(10,'TEST');
1 row created.
SQL> select * from T3;
ID NAME
---------- --------------------
10 TEST
SQL> c/3/4
1* select * from T4
SQL> /
ID NAME
---------- --------------------
10 TEST
SQL> insert into T3(name) values ('Chart');
1 row created.
SQL> c/3/4
1* insert into T4(name) values ('Chart')
SQL> /
1 row created.
SQL> select * from T3;
ID NAME
---------- --------------------
10 TEST
1 Chart
SQL> select * from T4
2 /
ID NAME
---------- --------------------
10 TEST
1 Chart
SQL> insert into T3 values(NULL,'AAA');
insert into T3 values(NULL,'AAA')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("DEMO"."T3"."ID")
SQL> c/3/4
1* insert into T4 values(NULL,'AAA')
SQL> /
1 row created.
SQL> select * from t4;
ID NAME
---------- --------------------
10 TEST
1 Chart
2 AAA
SQL> CREATE TABLE t5 (id NUMBER GENERATED ALWAYS AS IDENTITY , name varchar2(20));
Table created.
SQL> insert into T5 values(NULL,'AAA')
2 /
insert into T5 values(NULL,'AAA')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
SQL> insert into T5 values(1,'AAA')
2 /
insert into T5 values(1,'AAA')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
SQL> insert into T5(name) values('TEST');
1 row created.
SQL> select * from T5;
ID NAME
---------- --------------------
1 TEST
หวังว่าจะมีประโยชน์กับเพื่อนๆ นะครับ
ไม่มีความคิดเห็น:
แสดงความคิดเห็น