Lost relationships
-
This seems kind of weird, and I've never even heard of it happening to anyone, but it happens to me fairly regularly - SQL Server loses the relationships between some tables. I'm newly using 2014, and used 2008 R2 for many years previously. It happens in both.
I have no idea when or why, but when doing some development work, I look at the database diagram, and suddenly, there are lines missing. Not all - most are still in place, and it doesn't happen often enough for me to have noticed any sort of pattern as to which tables, which relationships, how many, when it happens or really anything. It's definitely not always the same table(s), and it's not always all the relationships to one table.
This is an extremely basic function of a database, and I can't imagine a product as mature and sophisticated as SQL Server having such a fundamental bug, and across two widely separated versions to boot. However, I can't figure out why this is happening. I am most definitely NOT doing it by hand. Nobody else does anything on this server - I am a one-man show here. Nobody else even has login privileges to the machine, let alone the expertise or inclination for any such specific and technically non-trivial mischief.
It is most definitely happening, though. I regularly print out the database diagram onto large sheets of paper, and paste them together into a very large wall chart, which I have on the wall next to my desk. It is a very handy quick reference, and makes a nice piece of wall art as well. The lines are there, printed out on this chart, so I'm not imagining that I put them in, or planned to and forgot. They really were there and suddenly they are not.
My only explanation is that I am doing something inadvertently during development that causes the relationships to be dropped, and I only notice it some time down the road, but I can't imagine what that would be. Can adding or removing fields or changing field types or removing and resetting schemabinding cause this to happen? (Besides the key fields, obviously - I'm not a complete idiot. I use meaningless long integer primary and foreign key fields exclusively, following a very specific naming convention, and I don't change those once a table is designed and put into service, ever.)
It's not a tragedy. The binding between the tables is more of a safety net than a primary line of defense. All the app code does tests and lookups, rather than blindly trying and crashing into database constraints, and activities that impact more than one table are always wrapped in a transaction, so even the dropped relationships have so far not caused any problems for the users. I always reinstate the relationships as soon as I notice them gone, and almost never run into anything that has skated through due to the lack of a binding relationship, but it's definitely making some inroads on my sanity.
My next thought is to create a job that scans all relationships in the database on a regular schedule and notifies me when any relationship has changed from the baseline, and maybe I'll at least be able to pinpoint when it is happening as a starting point for further troubleshooting. However, that will not be a trivial task. If anyone has any ideas before I start on that, I'd love to hear them.
- This topic was modified 4 years, 5 months ago by pdanes.
-
It doesn't just happen -- Somebody (e.g., editing table in SSMS or applying changes via a compare tool) or some process (e.g., SQL Agent job) is doing it.
You can track such changes w/ a DDL trigger that logs changes to a table -- e.g., https://www.sqlservercentral.com/articles/monitoring-changes-in-your-database-using-ddl-triggers.
-
Good article - thank you. I read through it and created the table and database trigger as it says. Seems to work just right - I removed one of my relationships, and it immediately logged a DROP CONSTRAINT, and when I put it back, it logged an ADD CONSTRAINT. So now I guess I wait and see, if one of these mysterious disappearances occurs again, and if this log table records anything useful.
-
Guessing.
It's SSMS. Someone is using the GUI to edit tables. They're getting warnings and ignoring them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
That someone would have to be me. I do get warnings about changes, sometimes, but I don't recall any about dropping relationships. Would it say so specifically, or does it just say that changes will be made, and I'm supposed to be smart enough to understand what those changes will be?
-
This was removed by the editor as SPAM
-
Before reading further, please understand that I am a rank noob . . . and I do mean RANK. I’ve done a couple of online tutorials and starting practicing, but have made only a little progress with much still to learn.
I got here because I was losing relationships created in diagrams. Searching found this thread. This thread took me to David Dye’s 2008 post, Monitoring Changes in Your Database Using DDL Triggers. I was able to follow that post and ran the queries/
Lo and behold it worked. Now I need help understanding the results.
My guess is that the CREATE and DROP TABLE references to dbo.Test resulted from my trial and error in running the queries. I don't understand what the last 2 CREATE/DROP TABLE dbo.Test mean. What other information might I expect if I run this against tables after trying to set relationshops in diagrams if those relationships disappear again.
Even as a rank noob, I can see great value in this tool. I’ve archived the queries because I’m certain to use them in the future.
Thank you, Mr. David Dye and Mr. SSCrazy Eights.
BTW: While trying to create this post, I clicked back to the Dye thread but when I clicked the back arrow to return to my post, text I had typed before was no longer there. Is this a quirk of this forum? Or, do I have yet another rabbit hole to explore?
Be kind. Be calm. Be generous. Behave.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply