October 8, 2020 at 6:20 pm
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.
October 8, 2020 at 7:44 pm
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.
October 8, 2020 at 8:46 pm
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.
October 9, 2020 at 1:00 pm
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 9, 2020 at 10:36 pm
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?
April 2, 2023 at 9:51 pm
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply