Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we may want it to come into play later when defining the Primary Key. For this, we will learn how to use “DEFERRABLE INITIALLY DEFERRED”.
But this does not mean that I will be able to add inconsistent data to the table. Yes, it can be added, but it will not be made permanent.
The reason is that in the worst case scenario, the primary key will come into play after the session is closed and will act in this way to eliminate the inconsistency.
Let’s create a new table.
1 2 3 4 5 | CREATE TABLE HAMZA8 ( CUST_ID8 NUMBER(2), CUST_NAME8 VARCHAR2 (15) ); |
Then I will add a primary key to the cust_id field in this table, but I won’t put it into effect right away.
1 | ALTER TABLE HAMZA8 ADD CONSTRAINT CUST_ID_PK8 PRIMARY KEY (CUST_ID8) DEFERRABLE INITIALLY DEFERRED; |
Then I try to insert things into the table that violate the primary key.
1 2 3 | INSERT INTO HAMZA8 VALUES (1,'onur'); INSERT INTO HAMZA8 VALUES (1,'burcu'); commit; |
It will do the first insert successfully.
It will do the second insert successfully.
When I press Commit, I get an error like the one below.
The disabled constraint is enabled as follows.
1 | set constraint cust_id_pk8 immediate; |
Afterwards, when I try new inserts, if it finds any contradictions, it will give an error at the insertion stage.
1 2 | INSERT INTO HAMZA8 VALUES (1,'ahmet'); INSERT INTO HAMZA8 VALUES (1,'onur'); |
Commit;
