Post reply

drop constraint fails

  • 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

  • November 22, 2007 at 7:10 pm

    #753492

    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)
    Intro to Tally Tables and Functions

  • November 23, 2007 at 4:48 am

    #753598

    no, nothing:crying:

  • November 23, 2007 at 4:57 am

    #753599

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

    John

  • November 23, 2007 at 6:16 am

    #753615

    not at all:crying:

  • November 23, 2007 at 4:06 pm

    #753707

    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)
    Intro to Tally Tables and Functions

  • November 26, 2007 at 12:11 am

    #753871

    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

  • November 26, 2007 at 8:49 am

    #754101

    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)
    Intro to Tally Tables and Functions

  • November 28, 2007 at 12:37 am

    #754840

    thanks for the reply and warm welcome

    Chris

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

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