Tricky Oracle Interview Question

 

we can achieve the  above functionality by using  DEFERRABLE INITIALLY DEFERRED as  below

CREATE TABLE TAB1(C VARCHAR2(5) PRIMARY KEY);

CREATE TABLE TAB2(C VARCHAR2(5) PRIMARY KEY);

ALTER TABLE TAB1 ADD CONSTRAINT TAB1_FK FOREIGN KEY(C) REFERENCES TAB2(C) DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE TAB2 ADD CONSTRAINT TAB2_FK FOREIGN KEY(C) REFERENCES TAB1(C) DEFERRABLE INITIALLY DEFERRED;

INSERT INTO TAB1 VALUES('A');

INSERT INTO TAB1 VALUES('B');

INSERT INTO TAB1 VALUES('C');

INSERT INTO TAB2 VALUES('A');

INSERT INTO TAB2 VALUES('B');

INSERT INTO TAB2 VALUES('C');

Output: