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.

Experiment On Primary Key

Today, We will do the experiment on Primary Key.

What is Primary Key?
The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique or it can be generated by the DBMS. Primary keys may consist of a single attribute or multiple attributes in combination.

Assuming we have a CUSTOMER Table with CUST_ID (PK), CUST_NAME columns. Now by any chance this CUST_ID will repeat in the CUSTOMER Table.

Consider the following example,

SQL> -- Experiment On Primary Key…

SQL> -- Creating Customer Table


SQL>create table customer(
2   cust_id number,
3   cust_name varchar2(15));


Table created.


SQL> -- Note: While creating the table PK was not defined.
SQL> -- Now populate the records for customer table


SQL> insert into customer values(101,’John Peter’);
1 row created.


SQL> insert into customer values(102,’Tom Kyte’);
1 row created.


SQL> insert into customer values(102,’Tom Kyte’);
1 row created.


SQL> commit;
Commit complete.


SQL> select * from customer;


CUST_ID CUST_NAME
------- ---------
101     John Peter
102     Tom Kyte
102     Tom Kyte




SQL>/* Note: In the CUSTOMER Table, CUST_ID - 102 was repeated, Okay becuase PK is not yet defined. */



SQL> -- Now, We are going to define the PK Constraint for CUSTOMER Table


SQL> alter table customer add constraint customer_pk
2 primary key(cust_id) disable;


Table altered.


SQL> create index cust_id_idx on customer(cust_id);


Index created.


SQL> alter table customer enable novalidate primary key;


Table altered.


SQL> -- Now PK Constraint has been defined and enabled for CUSTOMER
SQL>
SQL> -- Verifying the Data Dictionary…
SQL>


SQL>select table_name,constraint_name,constraint_type,status
2   from user_constraints
3   where table_name=’CUSTOMER’;



TABLE_NAME   CONSTRAINT_NAME  C  STATUS
----------   ---------------  -  -------
CUSTOMER     CUSTOMER_PK      P  ENABLED


SQL> /* All right now we have a table called CUSTOMER with primary key defined for column CUST_ID and the CUSTOMER_PK constraint status is ENABLED now.*/


SQL> select * from customer;


CUST_ID  CUST_NAME
-------  ----------
101      John Peter
102      Tom Kyte
102      Tom Kyte

Oops!, What happened?, for CUSTOMER table table we have defined the PRIMARY KEY and its STATUS is enabled but stil, it has duplicate records, CUST_ID - 102 was repeated and while enabling the Primary Key constraint also Oracle did not show any error, stating that your Existing records are duplicated.

SQL> — After enabling the constraint, We will try to insert the duplicate record
SQL> — Lets see what it is telling…


SQL> insert into customer values(101,’John Peter’);


insert into customer values(101,’John Peter’)
*
ERROR at line 1:ORA-00001: unique constraint (DEMO.CUSTOMER_PK) violated

Now if i am trying to insert a duplicate record, it is throwing an error, so constraint is validating the data while inserting new record.

What about the existing duplicate records?
Hmm, The key here is NOVALIDATE clause in the ALTER TABLE CUSTOMER ENABLE… command, the function of this clause is it will skip the validation for existing records while enabling the constraint.

Where it will be useful?, NOVALIDATE clause it will be frequently used in DWH environment by which the user will be TRUSTING the data (that is no validation is required for this data) and they will be loading into their tables.

Any constraint (PRIMARY KEY/FOREIGN KEY/UNIQUE/CHECK) which is defined in the table, will take extra effort to do the validation while you insert a record into table. But it is always Best Practice to define your constraints in the database level.

Hope you have enjoyed the experiment :-)

Validating your Data Entry for Trailing Blanks

Today, I would like to experiment the use of CHECK constraint to validate the trailing blanks in the data.
Example,

demo$Vino%orcl> create table t(name varchar2(20)
2 check (length(name)-length(trim(name))=0));


Table created.


demo$Vino%orcl> insert into t values('Tom');
1 row created.


demo$Vino%orcl> insert into t values('Tom Peter');
1 row created.


demo$Vino%orcl> -- Name with trailing blanks


demo$Vino%orcl> insert into t values('Tom ');
insert into t values('Tom ')
*
ERROR at line 1:
ORA-02290: check constraint (DEMO.SYS_C005409) violated


demo$Vino%orcl> select * from t;


NAME
----------
Tom
Tom Peter

Hope this will be useful for you :-)

Welcome Aboard...

This is Vinoth from India. I have been using Oracle since the year 2006. I have started my career as a Oracle PLSQL Developer. Now I am practicing as a Oracle DBA and willing to Learn, Research, Teach and Share Oracle related stuff.

My all time favorite hero is Tom Kyte (Vice President, Oracle Corporation). Learnt lot of things from him and still lot more are there.

In this blog, i will be posting oracle related experiments which i have experienced, and i am open to your commends/feedbacks. Please send your suggestions at vinothkumar.srinivasan@gmail.com

“Knowledge comes with experience and experimentation” by Tom Kyte Have a nice time.