weird - all my fk's disappeared?

  • I just experienced some weird behavior - All my FK's got dropped.

    I didn't explicitly delete any of my FK's. However, I normally add my FK's through database diagrams. For example, I create a new db diagram and use the designer to drag the relationships between tables. This seems to be the easiest way to add FK's in SSMS. At that point the FK's exist in the database so I figured I could simply delete the db diagrams. Does deleting the db diagrams delete the FK's as well? I wouldn't think SSMS should do this but that seems to be the only explanation. Please let me know your thoughts.....

  • This was removed by the editor as SPAM

  • 1) Never, ever, ever use SSMS GUI anything to do table object modifications. It is riddled with issues.

    2) Pull your gold-standard copy of the schema out of your source control system (you DO have a copy there, RIGHT?!?!) and do a diff compare and create the missing objects.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • This was removed by the editor as SPAM

  • I normally add my FK's through database diagrams. For example, I create a new db diagram and use the designer to drag the relationships between tables. This seems to be the easiest way to add FK's in SSMS.

    Easy & Risky as well... 😉

    How to: Delete Relationships (Visual Database Tools)

    http://msdn.microsoft.com/en-us/library/ms190185.aspx

  • sqlguy-736318 (11/6/2011)


    I normally add my FK's through database diagrams.

    Just adding the popular outcry allow me to say that ALL and EVERY change to the database should be done using a script - never, ever using a GUI tool. This is best practice and there is a reason why, script and log should be saved, they are re-usable, auditable and most important they are safer.

    Going back to the vanishing FKs... either they where never there or somebody/something dropped them. As simple as that.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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

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