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.


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.

Thursday, October 9, 2008

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’;
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 :-)

Saturday, October 4, 2008

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.

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 :-)

Monday, September 29, 2008

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

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