In this post I will described how to alter table and add new FOREIGN KEY constraints on table.Its very easy 3 steps work. After that you can add predefined trigger like on Delete, On updated, Not set etc.
Which is very useful if you want remove/update parent data with all relationship data which is stored in child table.
Step 1: first we add Column in child table.
ALTER TABLE Role ADD COLUMN Parent int(11) UNSIGNED DEFAULT NULL;
Step 2: first we add Index on foreign Key, because MySQL says foreign Key must Index.
ALTER TABLE Table1 ADD INDEX ParentTabl(ParentID)
Step 3: Now alter table and add constraints.
ALTER TABLE `ChildTbl` ADD CONSTRAINT `Parent` FOREIGN KEY(`Parent`) REFERENCES `ChildTbl`(`Id`)
Step 4: Now define trigger if you want add with constraints.
ALTER TABLE ` ChildTbl ` ADD CONSTRAINT `Parent` FOREIGN KEY(`Parent`) REFERENCES ` ChildTbl `(`Id`) ON DELETE CASCADE;
Note: If you are finding ‘Errno 150’ thats mean your PK column(s) in the parent table and the FK column(s) in child table is not same data type.