--------------------------------------------------------------------------------
-- Deferrable constraint

-- Cross referencing example:

-- Let aaa and bbb be tables,
-- such that aaa(y) references bbb(p) and bbb(q) references aaa(x).
--------------------------------------------------------------------------------




-- Create cross referencing tables
CREATE TABLE aaa
(
  x NUMBER PRIMARY KEY,
  y NUMBER,
  FOREIGN KEY(y) REFERENCES bbb(p)
);
CREATE TABLE bbb
(
  p NUMBER PRIMARY KEY,
  q NUMBER,
  FOREIGN KEY(q) REFERENCES aaa(x)
);

-- It will not work since bbb is undefined when creating aaa.
-- Therefore, aaa(y) cannot reference to bbb(p)





-- We have to use ALTER TABLE:
CREATE TABLE aaa
(
  x NUMBER PRIMARY KEY,
  y NUMBER
);
CREATE TABLE bbb
(
  p NUMBER PRIMARY KEY,
  q NUMBER,
  FOREIGN KEY(q) REFERENCES aaa(x)
);
ALTER TABLE aaa
  ADD CONSTRAINT aaa_ref_bbb
    FOREIGN KEY(y) REFERENCES bbb(p);





-- Try to insert data to bbb
INSERT INTO bbb VALUES(1,2);

-- INSERT INTO bbb VALUES(1,2)
--             *
-- ERROR at line 1:
-- ORA-02291: integrity constraint (SC42424.SYS_C002018) violated - parent key not found

-- Failed.




-- Similarly, try to insert data to aaa
INSERT INTO aaa VALUES(1,2)

-- INSERT INTO aaa VALUES(1,2)
--             *
-- ERROR at line 1:
-- ORA-02291: integrity constraint (SC42424.AAA_REF_BBB) violated - parent key not found

-- Failed again!






-- This chicken-and-egg problem can be solved by deferrable constraint.

-- Replace aaa_ref_bbb with deferrable constraint aaa_ref_bbb_def
ALTER TABLE aaa
  DROP CONSTRAINT aaa_ref_bbb;
ALTER TABLE aaa
  ADD CONSTRAINT aaa_ref_bbb_def
    FOREIGN KEY(y) REFERENCES bbb(p) INITIALLY DEFERRED DEFERRABLE;







-- Insert data with transaction

INSERT INTO aaa VALUES(9,1); 
-- No error since the constraint checking is deferred until commit.

SELECT * FROM aaa;
--          X          Y
-- ---------- ----------
--          9          1


INSERT INTO bbb VALUES(1,9);
-- No error since there exists a tuple (9,1) in aaa.


COMMIT;
-- Ther constraint aaa_ref_bbb_def is also checked at COMMIT.
-- Now we have (1,9) in bbb.  So aaa_ref_bbb_def is satisfied.

-- Success!


-- Last question: How to drop the tables?