Here I come again, after a while, long while. deferrable/ not deferrable adalah mekanisme pengecekan constraints: bila deferrable, maka constraint akan dicek saat setelah perintah commit. Sedangkan not deferrable, maka constraint dicek segera setelah perintah dml dieksekusi.
Saya mempunyai suatu konstrain primary key 'PK_CTLOG_H_LIST_MEDIA' pada kolom id_media di table CTLOG_H_LIST_MEDIA
Contoh:
SELECT table_name,
constraint_name,
status,
deferrable
FROM user_constraints
where constraint_name='PK_CTLOG_H_LIST_MEDIA'
ORDER BY table_name, constraint_name;
TABLE_NAME |CONSTRAINT_NAME |STATUS |DEFERRABLE
------------------|------------------------------|--------|--------------
CTLOG_H_LIST_MEDIA|PK_CTLOG_H_LIST_MEDIA |ENABLED |NOT DEFERRABLE
Lalu saya tes dengan query,
Sql> @ctlg_id_media;
Enter value for p_label_media: Dkmtr_20110421
ID_MEDIA |LABEL_MEDIA
----------|-----------------------------------
C99D-1336 |Dkmtr_20110421
Artinya rekor dengan id ‘C99D-1336’ sudah ada!
Sql> insert into CTLOG_H_LIST_MEDIA(id_media) values('C99D-1336');
Inserting
insert into CTLOG_H_LIST_MEDIA(id_media) values('C99D-1336')
*
ERROR at line 1:
ORA-00001: unique constraint (SPEAKER.PK_CTLOG_H_LIST_MEDIA) violated
Artinya settingan mekanisme “NOT DEFERRABLE” efektif.
Sebaliknya jika settingan saya robah,
Sql> ALTER TABLE CTLOG_H_LIST_MEDIA
2 ADD CONSTRAINT PK_CTLOG_H_LIST_MEDIA
3 PRIMARY KEY (id_media) DEFERRABLE INITIALLY DEFERRED;
Table altered.
Saya periksa dengan:
Sql> SELECT table_name,
2 constraint_name,
3 status,
4 deferrable, deferred
5 FROM user_constraints
6 where constraint_name='PK_CTLOG_H_LIST_MEDIA'
7 ORDER BY table_name, constraint_name;
TABLE_NAME |CONSTRAINT_NAME |STATUS |DEFERRABLE |DEFERRED
------------------------------|------------------------------|--------|--------------|---------
CTLOG_H_LIST_MEDIA |PK_CTLOG_H_LIST_MEDIA |ENABLED |DEFERRABLE |DEFERRED
Lalu saya masukkkan rekor yang ditolak tadi dengan:
Sql> insert into CTLOG_H_LIST_MEDIA(id_media) values('C99D-1336');
Inserting
1 row created.
Sql> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SPEAKER.PK_CTLOG_H_LIST_MEDIA) violated
Pengecekan akan efektif bila perintah “commit” dieksekusi. Itulah pengertian dan dampak dari “deferrable/ not deferrable”
