drop constraint fails

Question

Post reply

drop constraint fails

olli_d

Old Hand

Points: 309
More actions
November 22, 2007 at 1:23 pm

#184011

hi,
this is my first post to sqlservercentral after having read the newsletter for some time.
I'm struggeling with SQL Server 2005 not deleting a fk-constraint. But only one, the other drops of fk-constraints work fine with the same ado-connection with the same syntax.
this is how I delete the foreign keys
if exists (select * from dbo.sysobjects where name = 'FK_tdta_ebMatrix_tkey_eBaum')
ALTER TABLE [dbo].[tdta_EntscheidungsbaumMatrix] DROP CONSTRAINT FK_tdta_ebMatrix_tkey_eBaum
GO
this is how they were created
ALTER TABLE [dbo].[tdta_EntscheidungsbaumMatrix] WITH NOCHECK ADD CONSTRAINT [FK_tdta_ebMatrix_tkey_eBaum] FOREIGN KEY([fiBaum])
REFERENCES [dbo].[tkey_Entscheidungsbaum] ([idBaum])
ON DELETE CASCADE
NOT FOR REPLICATION
GO
I don't know what to do. What is weired with this constraint??
Just to make sure. This is another FK which I delete an reapply when deploying updates on the customer db
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tdta_AuswertungFeld_tkey_AuswertungFeld]'))
ALTER TABLE [dbo].[tdta_AuswertungFeld] DROP CONSTRAINT FK_tdta_AuswertungFeld_tkey_AuswertungFeld
GO
ALTER TABLE [dbo].[tdta_AuswertungFeld] WITH NOCHECK ADD CONSTRAINT [FK_tdta_AuswertungFeld_tkey_AuswertungFeld] FOREIGN KEY([fiAuswertung])
REFERENCES [dbo].[tkey_Auswertung] ([idAuswertung])
ON DELETE CASCADE
NOT FOR REPLICATION
GO
Hope you guys have some hints for me
Chris

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Do you get any error messages?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

olli_d Old Hand Points: 309 More actions · Answer 2

olli_d

Old Hand

Points: 309

November 23, 2007 at 4:48 am

#753598

no, nothing:crying:

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Do you have any DDL triggers defined on the database or server?

John

olli_d Old Hand Points: 309 More actions · Answer 4

olli_d

Old Hand

Points: 309

November 23, 2007 at 6:16 am

#753615

not at all:crying:

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Is this the constraint that you cannot delete?

if exists (select * from dbo.sysobjects where name = 'FK_tdta_ebMatrix_tkey_eBaum')

ALTER TABLE [dbo].[tdta_EntscheidungsbaumMatrix] DROP CONSTRAINT FK_tdta_ebMatrix_tkey_eBaum

GO

If so, what happens when you manually run the following...

ALTER TABLE [dbo].[tdta_EntscheidungsbaumMatrix] DROP CONSTRAINT FK_tdta_ebMatrix_tkey_eBaum

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

olli_d Old Hand Points: 309 More actions · Answer 6

executing the command manually worked properly. That is what made me mad. But also the if exists... works properly alone.

But I found it late saturday night!

See, I run the script from a vb6 application where I have the complete script in a vb6 resource file. I then split the script into pieces using the 'GO' as separator.

The problem was that I had a comment just before the problem statement which contained a GO. For the vb.split command it doesn't matter whether the GO is comment or sql. Makes sense right? That comment go messed up the if exists... and the drop constraint wasn't executed.

After all an easy solution now that I found it. Sorry for taking you into problems which nobody could solve due to not knowing the --comment-line 😉

Thanks

Chris

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Heh... thanks for sharing the solution!

It does show that in order to solve a problem, we sometimes need to know everything about the problem. But a fine line can exist between too much information and not enough.

Well done and, again, thanks for sharing what you came up with for a solution.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

olli_d Old Hand Points: 309 More actions · Answer 8

olli_d

Old Hand

Points: 309

November 28, 2007 at 12:37 am

#754840

thanks for the reply and warm welcome

Chris