CODEWITHSUNDEEP
sql-servert-sql
zanhtet
zanhtet

Reputation: 2050

The foreign key constraint

I have a table which contained the following columns.

ID int PK
Name nvarchar(50) NotNull
FID int FK reference to ID 

ID Name   PK
1  A      Null
2  B      Null
3  C      1
4  D      1
5  E      1
6  F      2

So, The primary key includes as a primary key in a table. I want to do that if the primary key is deleted, the rows which is contained the primary key as a foreign is deleted automatically. (example: When I delete ID 1 row, I want to delete automatically ID 3, 4, 5 rows.). How to make that the primary key is included as a foreign key in a table? How can I do this. Thanks.

Upvotes: 2

Views: 112

Answers (3)

Pyae Phyo Aung
Pyae Phyo Aung

Reputation: 848

NOTE: In Microsoft SQL, a cascading delete to a self-referencing table is not allowed. You must either use a trigger, create a stored procedure, or handle the cascading delete from the calling application. An example of this is where a single table has an ID as identity and a ParentID with a relationship to ID in the same table.

see here

Upvotes: 1

Jayanga
Jayanga

Reputation: 887

The only way will be to add a trigger you can refer the following links for more information.

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/b777ec73-e168-4153-a669-835049a96520

another link

Upvotes: 0

paulsm4
paulsm4

Reputation: 121849

You need to implement a "trigger" that does a "cascading delete".

Here's a good link:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=142564

CREATE TRIGGER test_trig

ON dbo.Table_1

FOR DELETE

AS
BEGIN
delete a from dbo.Table_2 a
JOIN
deleted d
ON a.joincol = d.joincol

END

Here are some other alternatives:

http://www.mssqltips.com/sqlservertip/1508/foreign-key-vs-trigger-referential-integrity-in-sql-server/

And here is a link to Microsoft's documentation on "Cascading Referential Integrity Constraints":

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

Upvotes: 3

Related Questions