wiered - Self Referential Key

Question

Post reply

wiered - Self Referential Key

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247183 More actions · Answer 1

You just have to remove the FK relationship which is preventing the deletion first:

update fk_test

set fno = null where fno = 5

delete fk_test where eno = 5

select * from fk_test

Mark Cowne One Orange Chip Points: 26986 More actions · Answer 2

You can do this in a single statement using MERGE

MERGE fk_test AS target

USING (SELECT 5) AS source(eno)

ON (source.eno IN (target.eno,target.fno))

WHEN MATCHED AND source.eno = target.eno THEN

DELETE

WHEN MATCHED AND source.eno = target.fno THEN

UPDATE SET fno = NULL;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

karthik M One Orange Chip Points: 29139 More actions · Answer 3

I want to achieve this without disabling FK.

is there any other way to do this?

Phil method looks ok.

Beside this, is it a good practice to design a table with self-referential integrity?

What is the alternate way to design the same table to achive this requirement?

karthik

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

karthik M (11/29/2012)
I want to achieve this without disabling FK.
is there any other way to do this?
Phil method looks ok.
Beside this, is it a good practice to design a table with self-referential integrity?
What is the alternate way to design the same table to achive this requirement?

Phil's methodology is the best way to execute deletes.

Furthermore, I don't think there is any issue with designing a table with self-refrential constraint. Take the example of emplyee: employee at the same time can be an employee and a manager both. This is implemented by slef-ref constraint on employee table.

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter