Dropping a table with a FK to its own PK on first try.

Question

Post reply

Dropping a table with a FK to its own PK on first try.

John Parlberg

SSC Rookie

Points: 44
More actions
September 5, 2007 at 9:32 pm

#125455

I'm having a hard time getting my drop tables routine to run and drop my table with an FK to its own PK.
I drop all the FKs first, and that works fine, in the next batch where I drop the tables I get the familiar error which reminds me to drop the FK first, which I've already done.
If I run the script again, then the tables drop just fine.
There must be a trick to force the SQL Server instance to recognize that the FK is really gone without having to run the script twice.
Getting the script to run the first time would be a charm.
John

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

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

michaela SSCertifiable Points: 5649 More actions · Answer 1

It sounds strange, most probably there's not any trick for getting the script run for once (BOL - Drop table topic)

I would say to write your code like: BEGIN TRAN ALTER TABLE...DROP CONTRAINT... ; first and then

BEGIN TRAN DROP TABLE...;

Ian Yates SSCoach Points: 19738 More actions · Answer 2

The cheating way would be to

1) Use Enterprise Manager / Management Studio (2000 vs 2005) to create a table and FK as per your arrangement

2) Save your work - have the table & FK created

3) Generate script to drop the table

Look at the script, you'll see what the tools do. I still have to develop in 2000 so I am reasonably familiar with what EM does - it has LOTS of begin/commit transaction commands. I wouldn't be surprised if that's all you needed, as per the prior comment.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

This is indeed weird. Dropping a table that has a foreign key referencing itself is allowed, and there should not be a warning. Only foreign keys from other tables are checked.

E.g.:

CREATE TABLE selfRef ( a INT PRIMARY KEY NOT NULL, b INT NULL REFERENCES selfref(a) )
GO
INSERT INTO selfRef VALUES (1,NULL)
INSERT INTO selfRef VALUES (2,1)
INSERT INTO selfRef VALUES (3,2)
INSERT INTO selfRef VALUES (4,1)
GO
DROP TABLE selfRef
GO

When you drop all your foreign keys, do you check for errors? Do they really drop all of them?

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

John Parlberg SSC Rookie Points: 44 More actions · Answer 4

I'll try the BEGIN TRANSACTION approach.

I've scripted the DROP FK and DROP TABLE from the menus, and have used them, however there's nothing I've seen to account for the FK self reference. The scripted code drops the FK's normally, then drops the table as one would expect, I just have to run the code twice to get the table to actually drop.

Yes, the first time the drop fk and drop table runs, it does return the error, but doesn't on the second try.

Thanks everyone for your prompt feedback.

John Parlberg SSC Rookie Points: 44 More actions · Answer 5

I tried to implement the BEGIN TRAN on the dropping of the FKs and the table, but didn't make a difference.

Is there another command that I'm missing like SET <something> on or off that I should be looking for to get the DROP CONSTAINT commands to be registered by the time the DROP TABLE is ready to be issued?