DDL - Deferring Constraint Checks

Pengecekan data pada constraint secara default akan di lakukan setiap perintah manipulasi data di execute (NOT DEFERREBLE) sebagai contoh seperti berikut:

SQL> insert into TEST_CONSTRAINT_CHECK(NIK, NAMA, SALDO, JENIS_KELAMIN)
VALUES ('010101', 'Dimas Maryanto', 0, 'l');

insert into TEST_CONSTRAINT_CHECK(NIK, NAMA, SALDO, JENIS_KELAMIN)
*
ERROR at line 1:
ORA-02290: check constraint (HR.CK_JK) violated

Dengan deferreble kita bisa hold sampai perintah commit atau rollback di execute, untuk menggunakan deferreble kita harus definisikan constraintnya seperti berikut:

Sekarang coba execute query berikut:

insert into TEST_CONSTRAINT_CHECK(NIK, NAMA, SALDO, JENIS_KELAMIN)
VALUES ('0202023', 'Test invalid saldo', -1, 'L');

insert into TEST_CONSTRAINT_CHECK(NIK, NAMA, SALDO, JENIS_KELAMIN)
VALUES ('0202022', 'Test Valid saldo', 10000, 'L');

Berikut hasilnya:

SQL> alter table TEST_CONSTRAINT_CHECK
    add constraint ck_saldo_always_abs check ( SALDO >= 0 )
        deferrable initially deferred;

Table altered.

SQL> select DEFERRABLE, STATUS
from USER_CONSTRAINTS
where TABLE_NAME = 'TEST_CONSTRAINT_CHECK'
  and lower(CONSTRAINT_NAME) = 'ck_saldo_always_abs';

DEFERRABLE     STATUS
-------------- --------
DEFERRABLE     ENABLED

SQL> insert into TEST_CONSTRAINT_CHECK(NIK, NAMA, SALDO, JENIS_KELAMIN)
VALUES ('0202023', 'Test invalid saldo', -1, 'L');

1 row created.

SQL> insert into TEST_CONSTRAINT_CHECK(NIK, NAMA, SALDO, JENIS_KELAMIN)
VALUES ('0202022', 'Test Valid saldo', 10000, 'L');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (HR.CK_SALDO_ALWAYS_ABS) violated

Back to top ↑