DDL - Enabled, Disabled & Drop Constraints

Sometime kita perlu me-disable constraint secara temporary untuk mempercepat proses seperti:

Batch insert operation,
Load data dalam jumlah yang besar
Import & Export semua data dalam table.
Melepas rule supaya bisa input data yang secara rule dilarang (exception)

Melakukan disabled constraint akan improve performance operation khususnya pada data warehouse configuration. Untuk mencari informasi constraint kita bisa menggunakan perintah berikut:

maka hasilnya seperti berikut:

C TABLE_NAME                     CONSTRAINT_NAME      STATUS   VALIDATED
- ------------------------------ -------------------- -------- -------------
R TEST_FK_DETAIL_TABLE           FK_DETAIL_MASTER_ID  ENABLED  VALIDATED
R TEST_CONSTRAINT_FK_OPTIONS_SET FK_CONSTRAINT_OPTION ENABLED  VALIDATED
  _NULL                          _SET_NULL

R TEST_CONSTRAINT_FK_OPTIONS_CAS FK_CONSTRAINT_OPTION ENABLED  VALIDATED
  CADE                           _CASCADE
C TEST_CONSTRAINT_NOTNULL        SYS_C007462          ENABLED  VALIDATED
C TEST_CONSTRAINT_MULTI_UNIQUE   SYS_C007469          ENABLED  VALIDATED
C TEST_CONSTRAINT_CHECK          CK_SALDO_ALWAYS_ABS  ENABLED  VALIDATED
C TEST_CONSTRAINT_CHECK          CK_JK                ENABLED  VALIDATED
C TEST_CONSTRAINT_CHECK_MULTI_CO CK_DISCOUNT_BETWEEN  ENABLED  VALIDATED
  LUMNS
C TEST_CONSTRAINT_CHECK_MULTI_CO CK_PRICE_AND_DISCOUN ENABLED  VALIDATED
  LUMNS                          T
C TEST_CONSTRAINT_PK             SYS_C007476          ENABLED  VALIDATED
C TEST_FK_DETAIL_TABLE           SYS_C007481          ENABLED  VALIDATED
C TEST_CONSTRAINT_FK_OPTIONS_SET SYS_C007489          ENABLED  VALIDATED
  _NULL
C TEST_CONSTRAINT_FK_OPTIONS_CAS SYS_C007493          ENABLED  VALIDATED
  CADE
U TEST_CONSTRAINT_UNIQUE         SYS_C007465          ENABLED  VALIDATED
U TEST_CONSTRAINT_MULTI_UNIQUE   UQ_PRODUCT_ID        ENABLED  VALIDATED
U TEST_CONSTRAINT_CHECK          SYS_C007473          ENABLED  VALIDATED
P TEST_CONSTRAINT_PK             SYS_C007477          ENABLED  VALIDATED
P TEST_CONSTRAINT_COMPOSITE_PK   PK_COMPOSITE_PRODUCT ENABLED  VALIDATED
P TEST_FK_MASTER_TABLE           SYS_C007479          ENABLED  VALIDATED
P TEST_FK_DETAIL_TABLE           SYS_C007482          ENABLED  VALIDATED
P TEST_CONSTRAINT_FK_OPTIONS     SYS_C007484          ENABLED  VALIDATED
P TEST_CONSTRAINT_FK_OPTIONS_SET SYS_C007490          ENABLED  VALIDATED
  _NULL
P TEST_CONSTRAINT_FK_OPTIONS_CAS SYS_C007494          ENABLED  VALIDATED
  CADE

32 rows selected.

Berikut adalah penjelasannya:

Field Constraint Type:

U, artinya Unique
P, artinya Primary Key
C, artinya Check
R, artinya References atau Foreign Key

Field Status:

ENABLED = aktif
DISABLED = mati

Field Validated:

VALIDATED, di check secara berkala atau operation. Dengan tujuan menjaga bahwa data baru dan lama valid semua
NOT VALIDATED, ketika di input di check tetapi jika data usah ada tidak di check.

Untuk melihat data definition language yang telah di buat, kita bisa menggunakan query berikut:

berikut hasilnya:

Enter value for const_name: 'CK_PRICE_AND_DISCOUNT'

  ALTER TABLE "HR"."TEST_CONSTRAINT_CHECK_MULTI_COLUMNS" 
  ADD CONSTRAINT "CK_PRICE_AND_DISCOUNT" 
  CHECK ( price >= 10000 and discount >= 0.1 ) ENABLE

Disable Constraint

Secara default begitu contraint di buat maka akan automatis enabled jika mau disable kita bisa menggunakan alter table seperti berikut:

Dengan begitu kita bisa input, price <= 10000 and discount <= 0.1 seperti berikut:

insert into test_constraint_check_multi_columns(product_id, name, price, discount)
VALUES (1, 'Test price discount lower than', 10000, .01);

hasilnya seperti berikut:

SQL> insert into test_constraint_check_multi_columns(product_id, name, price, discount)
VALUES (1, 'Test price discount lower than', 10000, .01);  2

1 row created.

SQL> select * from test_constraint_check_multi_columns where product_id = 1;

PRODUCT_ID NAME                                                    PRICE   DISCOUNT
---------- -------------------------------------------------- ---------- ----------
         1 Test price discount lower than                          10000        .01
         1 Macbook Pro 13" (2017)                                  25000         .2
         1 Test discount null                                      25000

3 rows selected.

SQL> commit;

Commit complete.

Enabled Constraint

Untuk mengaktifkannya kembali, sama kita bisa menggunakan alter table seperti berikut:

Jika di execute ini akan terjadi error, karena ada data yang tidak sesuai criteria check contraint tersebut seperti berikut:

SQL> alter table TEST_CONSTRAINT_CHECK_MULTI_COLUMNS
    enable constraint CK_PRICE_AND_DISCOUNT;
                      *
ERROR at line 2:
ORA-02293: cannot validate (HR.CK_PRICE_AND_DISCOUNT) - check constraint violated

solusinya kita bisa menggunakan NOVALIDATE seperti berikut:

Hasilnya seperti berikut:

SQL> alter table TEST_CONSTRAINT_CHECK_MULTI_COLUMNS
    enable novalidate constraint CK_PRICE_AND_DISCOUNT;  2

Table altered.

SQL> select CONSTRAINT_TYPE, TABLE_NAME, CONSTRAINT_NAME, STATUS, VALIDATED
from USER_CONSTRAINTS
where CONSTRAINT_NAME = 'CK_PRICE_AND_DISCOUNT'
  and OWNER = user;  2    3    4

C TABLE_NAME                     CONSTRAINT_NAME      STATUS   VALIDATED
- ------------------------------ -------------------- -------- -------------
C TEST_CONSTRAINT_CHECK_MULTI_CO CK_PRICE_AND_DISCOUN ENABLED  NOT VALIDATED
  LUMNS                          T

1 row selected.

Drop Constraint

Constraint yang telah di buat gak bisa di modifikasi, untuk mengubah rulenya kita harus drop dulu kemudian di buat baru lagi. Untuk menghapus constraint kit bisa menggunakan drop constraint seperti berikut:

maka hasilnya seperti berikut:

SQL> alter table TEST_CONSTRAINT_CHECK_MULTI_COLUMNS
    drop constraint CK_PRICE_AND_DISCOUNT;

Table altered.

select CONSTRAINT_TYPE, TABLE_NAME, CONSTRAINT_NAME, STATUS, VALIDATED
from USER_CONSTRAINTS
where CONSTRAINT_NAME = 'CK_PRICE_AND_DISCOUNT'
  and OWNER = user;

no rows selected