mysql - Foreign Key Tables Updates -
i started learning sql bit confused.
if have table has primary key : customerid & table b foreign key customerid
i added foreign key constraint using cascade foreign key should update or delete automatically when primary key deleted or updated.
however, works delete. when add together new record in primary field table, record not shown in foreign key table, why ?
corresponding rows updated or deleted in referencing table when row updated or deleted in parent table. cascade cannot specified if timestamp column part of either foreign key or referenced key. on delete cascade cannot specified table has instead of delete trigger. on update cascade cannot specified tables have instead of update triggers.
as mention in msdn. have mentioned update , delete operation of primary key table impact foreign key table's column. if insert made primary key, not affected foreign key. since main objective in primary key , foreign key relationship
"an each every record available in foreign key table, should contain corresponding record should nowadays in primay key table , vice versa not applicable".
if insert record foreign key table throws foreign referential integrity error. not allows insert record in foreign table unless , until corresponding record in primary key table.
for info take in next in msdn links
http://msdn.microsoft.com/en-us/library/ms179610.aspx
note:
if want accomplish functionality have write logic in stored procedure or trigger.
mysql sql sql-server constraints