ทดสอบ 12c Default Values สำหรับคอลัมน์ (Sequence และ Explicit NULL Insertion)

บทความนี้ไม่ต้องการเกริ่นอะไรมาก ดังที่เราทราบกันดีว่า ฐานข้อมูลออราเคิล 12c เราสามารถเซตคอลัมน์กับ default values (บน Sequence และ Explicit NULL Insertion).
ออราเคิล sequence - เราสามารถระบุ sequence.CURVAL หรือ sequence.NEXTVAL
ออราเคิล Explicit NULL Insertion (ON NULL...) - เราสามารถระบุค่า default ที่เราต้องการเมื่อ INSERT กับค่า NULL  
ตัวอย่าง ผมขอใช้ทั้งสองกรณีในทีเดียวเลยนะครับ

SQL> CREATE SEQUENCE S1 START WITH 1;

Sequence created.

SQL> CREATE TABLE TB (ID NUMBER DEFAULT S1.NEXTVAL NOT NULL, NAME VARCHAR2(30), ADDR VARCHAR2(50) DEFAULT ON NULL 'N/A' NOT NULL);

Table created.

SQL> DESC TB
 Name                                                                                Null?    Type
 --------------- -------- --------------------------------------------------------
 ID                                                                                  NOT NULL NUMBER
 NAME                                                                                         VARCHAR2(30)
 ADDR                                                                                NOT NULL VARCHAR2(50)

SQL> INSERT INTO TB(NAME) VALUES('SURACHART OPUN');

1 row created.

SQL> INSERT INTO TB(NAME) VALUES('TEST!!!');

1 row created.

SQL> SELECT * FROM TB;

        ID NAME                           ADDR
---------- ------------------------------ --------------------------------------------------
         1 SURACHART OPUN                 N/A
         2 TEST!!!                        N/A

อ่านเพิ่มเติม Oracle Documents.

เรียนสร้างเทเบิลบนฐานข้อมูลออราเคิล 12c กับ Identity Column

วันนี้ได้มีโอกาสอ่านเกี่ยวกับการสร้างเทเบิลกับ  Identity คอลัมน์ ผมเกิดความสงสัยเลยลองหาข้อมูลและลองทดสอบดู จริงๆ แล้วคล้ายกับการที่เราสร้างเทเบิลแล้วมีการระบุ Sequence ให้กับคอลัมน์ที่ต้องการ อย่างไร? คิดง่ายๆ ว่า Option นี้ เข้ามาช่วยเราในการที่ไม่ต้องสร้าง Sequence เอง...

จะเห็นว่าเราสามารถเลือก ALWAYS (Oracle จะ generate ค่าของ sequence ทุกๆครั้ง เราไม่สามารถระบุค่าเองให้กับคอลัมน์นี้) และ BY DEFAULT (Oracle จะ generate ค่าของ sequence และเรายังสามารถระบุค่าในคอลัมน์นี้ได้เอง แต่ไม่สามารถระบุค่า NULL ยกเว้นเราใช้ BY DEFAULT ON NULL)
จำไว้ว่า  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

หวังว่าจะมีประโยชน์กับเพื่อนๆ นะครับ

12c กับคอลัมน์ INVISIBLE และ set colinvisible

ฐานข้อมูลออราเคิล 12c นั้นเราสามารถเซต COLUMN ที่เราไม่ต้องการนั้น Invisible (ไม่ปรากฏหรือโชว์เมื่อเราดึงข้อมูล) แต่เรายังสามารถ INSERT/UPDATE ข้อมูลใน COLUMN (Invisible) นั้นได้ ...แถมยังเอาไปใช้สร้าง INDEX ได้อีกด้วย

SET COLINVISIBLE
ใช้ในการโชว์ข้อมูล column ที่ invisible

ตัวอย่าง

SQL> show colinvisible
colinvisible OFF
SQL>
SQL>  create table mytable(col1 number, col2 varchar2(20) invisible);

Table created.

SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

SQL> insert into mytable (col1,col2) values (1,'Surachart Opun');

1 row created.

SQL> select * from mytable;

      COL1
----------
         1

SQL>
SQL> show colinvisible
colinvisible OFF
SQL>
SQL> set colinvisible on
SQL> show colinvisible
colinvisible ON
SQL>
SQL>
SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2 (INVISIBLE)                                   VARCHAR2(20)

SQL>
SQL>  select * from mytable;
      COL1
----------
         1

SQL> select COL1, COL2 from mytable;

      COL1 COL2
---------- --------------------
         1 Surachart Opun

SQL>
SQL> set colinvisible off
SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

SQL>
SQL>
SQL> select COL1, COL2 from mytable;

      COL1 COL2
---------- --------------------
         1 Surachart Opun

จะพบว่าเราสามารถ INSERT/UPDATE หรือ SELECT ข้อมูลจาก column ที่ invisible ได้ด้วยการระบุชื่อ column

อย่างไรก็ดี COLUMN ที่เราต้องการเซตเป็น INVISIBLE ไม่ควรมี default (NOT NULL)

SQL> create table mytable(col1 number, col2 varchar2(20) not null);

Table created.

SQL> alter table mytable modify (col2 invisible);

Table altered.

SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

SQL> insert into mytable values (1);
insert into mytable values (1)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("DEMO"."mytable"."COL2")


SQL>
SQL>
SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

SQL>
SQL> show colinvisible
colinvisible OFF
SQL> set colinvisible on
SQL>
SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2 (INVISIBLE)                          NOT NULL VARCHAR2(20)

SQL>
SQL> insert into mytable values (1,'Surachart Opun');
insert into mytable values (1,'Surachart Opun')
            *
ERROR at line 1:
ORA-00913: too many values


SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2 (INVISIBLE)                          NOT NULL VARCHAR2(20)

SQL>  insert into mytable (col1,col2) values (1,'Surachart Opun');

1 row created.

จะเห็นว่าเกิด Error ดังตัวอย่าง... เราจึงควรเช็คให้ดี หรือแจ้งให้ Developer ทราบ ^______________^