November 6, 2011 at 8:54 pm
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.....
November 7, 2011 at 12:43 am
This was removed by the editor as SPAM
November 7, 2011 at 8:51 am
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
November 8, 2011 at 12:41 am
This was removed by the editor as SPAM
November 8, 2011 at 1:01 am
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)
November 8, 2011 at 5:54 am
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