on delete cascade v/s on delete restrict

27 Aug 2009

For setting Foreign key in MySql both the tables should be innoDB.

1.Alter table ENGINE="INNODB"

      this query will convert table into innoDB
 2. Consider two tables
      1.parent

idname
1kites
         2.child
idaddress
1cochin
3.set the foreign key with on delete cascade

    alter table  child add foreign key (id) references parent(id) on delete cascade.
            If you set a foreign key with  on delete cascade  then
    1.first tou want to insert data into parent then to child.
    2.You  can delete record from child independently but you can't insert into child without corresponing record in parent.
    3.If you delete a record from  parent table it will delete from both parent and child table without any warning. 
Eg: if you try to delete a record from parent with id 1 it will also delete the  record from child whose id=1


4.set the foreign key with on delete restrict 
    alter table  child add foreign key (id) references parent(id) on delete restrict. 
   If you set a foreign key with  on delete restrict  then

    1.first you want to insert data into parent then to child.
    2.You  can delete record from child independently but you can't insert into child without corresponing record in parent.
    3.It will restrict deletion from parent tables if a corresponding child record is available.  
     4.I f you want to delete from parent table then you want to delete record from child table first



If you particularly enjoy my work, I appreciate donations given with Gittip.