Post reply

Delete Orphaned Constraints

  • November 21, 2012 at 1:48 pm

    #270425

    Hello Everyone

    I am working with a database and have found that there are a couple constraints that are no longer with a table. One Primary Key and one Foreign Key. I would like to simply delete the orphaned constraints. Is there a way to do this?

    Thanks in advance for all your help

    Andrew SQLDBA

  • November 21, 2012 at 2:28 pm

    #1561404

    Not sure what you mean by "no longer with the table"..? if the tables were dropped, the constraints would have gone with it, unless they were a default, created separately and "bound" to the table / column.

    Maybe I am missing something ?

  • November 21, 2012 at 2:42 pm

    #1561411

    a SQL 2000 database, or one that was in SQL 2000 in a previous life?

    in that version, you could hand-edit the sys tables and drop objects without cleaning up the constraints....

    is that what we hav ehere?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 21, 2012 at 3:18 pm

    #1561421

    Yes, I remember those days, things were more simple then.

    The table was dropped, but a couple constraints were not dropped. But I did find out that the code that created those constraints was created by a DB2 person. They did not code it correctly to create them, so yes, they were the Defaults.

    I found a slightly faster way of clearing this problem. I dropped and re-created the database, and then simply did an alter on the table to create some new columns.

    They had dropped the table and tried to create it again, with the same constraint name. I had it out with the DB2 person about how to perform actions within SQL Server. We do not drop objects.

    Thanks guys, I hope that you all have a very nice day

    Andrew SQLDBA

  • November 21, 2012 at 11:00 pm

    #1561480

    AndrewSQLDBA (11/21/2012)

    We do not drop objects.

    Why not? If an object needs to be dropped, what's wrong with dropping it? And, during development, I conditionally drop objects and rebuild them all the time with and without constraints.

    To reiterate what Nagabhushan stated, when you drop a table, any constraints go with it. The constraints cannot be recreated until the table is recreated. If you have "orphaned" constraints, then there's a very serious bug in SQL Server. Are you absolutely sure that the table was actually dropped or are you talking about an error you got back from some code that they were running to try to do this? People frequently forget that you sometimes have to drop certain constraints before you can drop a table and both the table and the constraint continues to persist.

    --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)

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

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