Saturday, November 15, 2008

Chicken-Egg Problem

Sometimes, it is very important to DEFER the checking of certain integrity constraints. “Chicken-and-Egg” is the best example for this.

Experiment,

The above diagram shows the Egg and Chicken table, their attributes and their relationship. But, If we try to create the table for the above Data Model we will end up with an error. The reason is that the CREATE TABLE statement for CHICKEN table refers to the EGG table, which has not been created yet. Creating EGG table will not help either, because EGG table referes to CHICKEN table.

To give solution to this problem, we need SQL Schema modification commands. Consider the following solution,

SQL> -- Create CHICKEN and EGG table without FOREIGN KEY Constraint
SQL> create table chicken( 
2    chicken_id number primary key,
3    egg_id number);

Table created.
SQL> create table egg( 2 egg_id number primary key, 3 chicken_id number);
Table created.
SQL> -- Add the FOREIGN KEY Constraint SQL> alter table chicken add constraint chicken_ref_egg 2 foreign key(egg_id) references egg(egg_id) 3 initially deferred deferrable;
Table altered.
SQL> alter table egg add constraint egg_ref_chicken 2 foreign key(chicken_id) references chicken(chicken_id) 3 initially deferred deferrable;
Table altered.
SQL> /* NOTE: “INITIALLY DEFERRED DEFERRABLE” clause in the ALTER command tells SQL> oracle to do deferred constraint checking */
Okay, We have created the CHICKEN and EGG table with CROSS relationship. Now, How we are going to insert a records into these tables. Becuase, if we want to insert a record into CHICKEN table, EGG table should have been already populated and vice versa. Consider the following two scenarios,

SQL> –- Case 1: CONSISTENT Records

SQL> insert into chicken(chicken_id,egg_id) values (1,2);
1 row created.

SQL> insert into egg(egg_id,chicken_id) values(2,1);
1 row created.

SQL> commit;
Commit complete.
Wow, Inserted Successfully. Constraints has been Validated?, Not Yet. When it will validate? Because, We have declared the FOREIGN KEY Constraints as “DEFERRED”, they are only checked at the COMMIT point. Without DEFERRED constraint checking, we cannot insert anything into CHICKEN and EGG table, becuase the first INSERT would always be a Constraint Violation

SQL> – Case 2: INCONSISTENT Records.

SQL> insert into chicken(chicken_id,egg_id) values (101,102);
1 row created.
SQL> insert into egg(egg_id,chicken_id) values(103,104);
1 row created.
SQL>-- Hmm, Records are inconsistent but it was inserted. SQL>-- Validation Done for inconsistent records? We need to wait till COMMIT point
SQL> select * from chicken;
CHICKEN_ID EGG_ID ---------- ------ 1 2 101 102
SQL> select * from egg;
EGG_ID CHICKEN_ID ------ ---------- 2 1 103 104
SQL> commit;
commit * ERROR at line 1: ORA-02091: transaction rolled back
SQL> --Oops, INCONSISTENT records has been Rolled Back. Verify the result,
SQL> select * from chicken;
CHICKEN_ID EGG_ID ---------- ------ 1 2
SQL> select * from egg; EGG_ID CHICKEN_ID ------ ---------- 2 1
Hope, you have enjoyed the experiment. Share your comments.

No comments: