2

I just started learning SQL so I am a bit confused.

If I have Table A that has a primary key : CustomerID & Table B with foreign key CustomerID

I added the foreign key constraint by using CASCADE so that the foreign key should update or delete automatically when primary key is deleted or updated.

However, it only works for delete. When I add a new record in the primary field table, that record is not shown in the foreign key table, why is that ?

ngrashia
  • 9,869
  • 5
  • 43
  • 58
Lenonor
  • 47
  • 7

2 Answers2

1

Corresponding rows are updated or deleted in the referencing table when that row is updated or deleted in the parent table. CASCADE cannot be specified if a timestamp column is part of either the foreign key or the referenced key. ON DELETE CASCADE cannot be specified for a table that has an INSTEAD OF DELETE trigger. ON UPDATE CASCADE cannot be specified for tables that have INSTEAD OF UPDATE triggers.

As mention in MSDN. They have mentioned the only update and delete operation of primary key table will affect the Foreign key table's column. If any insert made to primary key, it will not affected the foreign key. Since the Main objective in primary key and foreign key relationship

"An each every record is available in the foreign key table, it should contain corresponding record should be present in the primay key table and vice versa is not applicable".

If you insert any record to foreign key table that it will throws foreign referential integrity error. It will not allows you to insert a record in the foreign table unless and until you will corresponding record in the primary key table.

for information take look in following in MSDN links

http://msdn.microsoft.com/en-us/library/ms179610.aspx

Note:

if you want to achieve this functionality then you have write a logic in Stored procedure or trigger.

Thangamani Palanisamy
  • 5,152
  • 4
  • 32
  • 39
0

No,is not automatic add in your foreign key table , it's not make sense , for example if you have two table ,"City" and "People" , People in the City , so there must be a FK refer for People , if you add record in City e.g. New York , How is database know who's need to insert to People table?How many people? , and what this people properties? e.g. People Name?

So database can't do that automatic , you have to do it manually!

Vincent
  • 81
  • 6