Referential integrity constraint in MySQL

Hey guys!
This post is response to article "Using foreign keys and referential integrity in MySQL" at http://www.builderau.com.au/program/mysql/soa/Using-foreign-keys-and-referential-integrity-in-MySQL/0,339028784,339237600,00.htm

Following is a summary of how to use foreign key constraint in MySQL.

Three condition should met in accordance to use foreign key constraint among tables in MySQL:-

• INNOBD type
  

  Both table should be of INNOBD type

  
  
• Field my be indexed
  

  Field used in the foreign key constraint should be indexed.

  
  
• Data type should be similar
  

  Data types of the field used in the foreign key relationship should be similar.
  
  

Syntax for this:-
lets assume we have two tables parent and child.

• Creating parent table
  

  create table parent(id int NOT NULL Auto_Increment PRIMARY KEY,f_name varchar(15))ENGINE=INNODB;

  
  
• Createing child table
  

  create table child(id int NOT NULL,l_name varchar(15))ENGINE=INNODB;

  .
  
  
• adding foreign key constraint
  

  ALTER TABLE child
  ADD INDEX(id),
  FOREIGN KEY(id) REFERENCES parent(id);

  
  Note: we can define foreign key constraint while defining table as:
  

  create table child(id int NOT NULL,l_name varchar(15),INDEX(id),FOREIGN KEY(id) REFERENCES parent(id));
  

Significance of Foreign key Constraint!

Foreign key constraint help us in achieving Referential Integrity in MySQl. With help to reduce redundancy in database as record can be added to reference(child) table is corresponding field value exist in the refereed(parent) table.

Lets add values in to the table created above
insert into parent values (1,'John'),(2,'Peter'),(3,'William');
Now if this command is executed
insert into child values (4,'walter');
this will give an error as there is no record in parent table with field id value 4.

ON DELETE CASCADE on UPDATE CASCADE

MySQL require above keyword in foreign key constraint if user want to delete corresponding record in child table when a record in parent table is deleted.

syntax for this:

create table child(id int NOT NULL,l_name varchar(15),INDEX(id),FOREIGN KEY(id) REFERENCES parent(id) ON DELETE CASCADE ON UPDATE CASCADE);