wiered - Self Referential Key

Question

Post reply

wiered - Self Referential Key

karthik M

One Orange Chip

Points: 29139
More actions
November 29, 2012 at 2:23 am

#285606

All,
I have recently faced the below scenario.
create table fk_test
(
eno int primary key,
fno int
)
alter table fk_test
alter table fk_test
add constraint fk_test1 foreign key ( fno )
references fk_test ( eno )
insert into fk_test
select 1,5
union
select 5,1
delete from fk_test
where eno
in ( select eno from fk_test
where fno = 5 )
delete from fk_test
where fno = 5
delete from fk_test
where fno = 1
My requirement is to delete the eno '5' from the table.
I just tried all the combinations.
delete from fk_test
where eno
in ( select eno from fk_test
where fno = 5 )
delete from fk_test
where fno = 5
delete from fk_test
where fno = 1
But No Luck...
Note I don't want to disable foreign key for this deletion.
is it possible? "No" ..is the answer..it is a kind of cyclic situation..although i just wanted to know is there any corner to reach this... 🙂
karthik

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

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

Phil Parkin SSC Guru Points: 246979 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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Mark Cowne One Orange Chip Points: 26954 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