What use are foreign keys anyway?
-
A colleague today asked me to show him the database structure for an application I wrote a couple of years ago, for maintenance purposes. I directed him to the database diagram I had helpfully created in SQL Server. Sadly, upon opening it, it became apparent that someone had for some unknown reason decided to remove all the relationships between all the tables, for no obvious reason. I despair sometimes.
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
Haha.. this is funny - not! I've come across this all the time. The problem "back then" were application designers that had no idea, zilch, nada; about database structures. Yet, here we are in 2018 and I still see the same mistakes. Primary key every table with an auto-fill ID column. Yes, you have a primary key, congratulations! But what's the point if you don't have a reference to the ID from another table? Did someone forget that the ID Primary Key column is a machine necessity, if at all? Like who care my next record starts with ID 2001!? A true Primary Key is one created based on data "snippets" off other columns in the table. Yeah baby... bring back Excel databases! :confused: :rolleyes: Just do what I do when databases have no relations... DROP DATABASE.. oh but don't forget to save your data first. :-O
-
Haha.. this is funny - not! I've come across this all the time. The problem "back then" were application designers that had no idea, zilch, nada; about database structures. Yet, here we are in 2018 and I still see the same mistakes. Primary key every table with an auto-fill ID column. Yes, you have a primary key, congratulations! But what's the point if you don't have a reference to the ID from another table? Did someone forget that the ID Primary Key column is a machine necessity, if at all? Like who care my next record starts with ID 2001!? A true Primary Key is one created based on data "snippets" off other columns in the table. Yeah baby... bring back Excel databases! :confused: :rolleyes: Just do what I do when databases have no relations... DROP DATABASE.. oh but don't forget to save your data first. :-O
Fandango90 wrote:
A true Primary Key is one created based on data "snippets" off other columns in the table.
The "natural vs surrogate primary key" debate is like tabs vs spaces - some people insist that there's only one "correct" way to do, whilst others make a decision on a case-by-case basis. :) SQL Server: Natural Key Verses Surrogate Key — DatabaseJournal.com[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Last week I added a few fields to a table, no problem. Yesterday we got the data and I realized I needed to change the type of one field in said table as the data was a bit different than I had expected. No problem I think, we haven't added any data to that field yet, so I enter design mode, change the type and save the changes. Up comes a popup stating it cannot rewrite the table as there are several tables depending on it. No problems I think again, I cancel out of it and decide to change the table the next day using DDL instead. And today to my surprise I see that the table does not have any foreign keys anymore. Go figure. The takeaway is to not trust the designer mode of SSMS. <edit>I haven't tested it fully yet, but it seems like it might happen when you have an indexed view on the table
Wrong is evil and must be defeated. - Jeff Ello
When I have that problem, I rename the column with a suffix of "_DELETE" and create a new column with the old name and newly desired characteristics. The designer will let me do that without dropping and recreating the table. After I have migrated any data and updated any involved stored procedures and entity framework models, I delete the old column.
-
When I have that problem, I rename the column with a suffix of "_DELETE" and create a new column with the old name and newly desired characteristics. The designer will let me do that without dropping and recreating the table. After I have migrated any data and updated any involved stored procedures and entity framework models, I delete the old column.
I simply don't use the designer any more.
Wrong is evil and must be defeated. - Jeff Ello
-
Haha.. this is funny - not! I've come across this all the time. The problem "back then" were application designers that had no idea, zilch, nada; about database structures. Yet, here we are in 2018 and I still see the same mistakes. Primary key every table with an auto-fill ID column. Yes, you have a primary key, congratulations! But what's the point if you don't have a reference to the ID from another table? Did someone forget that the ID Primary Key column is a machine necessity, if at all? Like who care my next record starts with ID 2001!? A true Primary Key is one created based on data "snippets" off other columns in the table. Yeah baby... bring back Excel databases! :confused: :rolleyes: Just do what I do when databases have no relations... DROP DATABASE.. oh but don't forget to save your data first. :-O
Fandango90 wrote:
the same mistakes. Primary key every table with an auto-fill ID column
Sorry, I don't see how this is a mistake. :confused:
Fandango90 wrote:
Yes, you have a primary key, congratulations! But what's the point if you don't have a reference to the ID from another table?
I think you may have misunderstood the OP's issue. They didn't de-normalize, only removed the FK constraints.
Fandango90 wrote:
Did someone forget that the ID Primary Key column is a machine necessity, if at all? Like who care my next record starts with ID 2001!?
I'm not even sure what you mean by 'machine necessity'. Perhaps it's sarcasm and I'm not getting it. :confused:
Fandango90 wrote:
A true Primary Key is one created based on data "snippets" off other columns in the table
Again, I can't tell if this is sarcasm or a real opinion. :confused: So an identity or guid is not a true PK, but a couple of columns where one or more values can change is??? (or, just throw in a timestamp to be sure!) It sure makes future record maintenance a lot easier when I can tag a record with a single condition. Anyway, it would probably make an interesting poll. :)
"Go forth into the source" - Neal Morse
-
CodeWraith wrote:
Some dimwit tries to insert or delete something and bounces off these foreign key constraints. Instead of adapting the application logic to take the constraints into account, the harebrain throws the constraints (and the database's integrity) out the window.
First job out of college, I was that dimwit Jr Developer. We needed to delete a few items and add a few new ones. You guessed it, I ran into the constraints. So I asked the Sr. Dev, he said to drop the keys, add and remove the items, then re-add the keys. Being a good student, I followed his advice. At least I was smart enough to use the automated generate Drop/Add script functionality in SQL so I didn't screw it up THAT much. Learning through mistakes.
Be careful of the "Script To..." option. Pay Attention to What "Script...To" Generates[^]
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
Fandango90 wrote:
the same mistakes. Primary key every table with an auto-fill ID column
Sorry, I don't see how this is a mistake. :confused:
Fandango90 wrote:
Yes, you have a primary key, congratulations! But what's the point if you don't have a reference to the ID from another table?
I think you may have misunderstood the OP's issue. They didn't de-normalize, only removed the FK constraints.
Fandango90 wrote:
Did someone forget that the ID Primary Key column is a machine necessity, if at all? Like who care my next record starts with ID 2001!?
I'm not even sure what you mean by 'machine necessity'. Perhaps it's sarcasm and I'm not getting it. :confused:
Fandango90 wrote:
A true Primary Key is one created based on data "snippets" off other columns in the table
Again, I can't tell if this is sarcasm or a real opinion. :confused: So an identity or guid is not a true PK, but a couple of columns where one or more values can change is??? (or, just throw in a timestamp to be sure!) It sure makes future record maintenance a lot easier when I can tag a record with a single condition. Anyway, it would probably make an interesting poll. :)
"Go forth into the source" - Neal Morse
A relation may have multiple candidate keys, any one of which may be selected as primary key. Any attribute that is subject to mutation is not suitable - because they simply cannot be used to identify a row. The "A true PK is one created on data "snippets" off other columns in the table" is explicitly the wrong way round in terms of normalisation. Every other column should be dependent on the whole of every candidate key, and have no dependencies on anything else. Here, Fandango seems to be proposing exactly the opposite - having the PK dependent on every other column, which is pure insanity, and worthy of an entry in this forum all by itself ;-)
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
-
Be careful of the "Script To..." option. Pay Attention to What "Script...To" Generates[^]
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013Thank you for the heads up. Haven't run into that before, at least that I've ever noticed, but will start to check from now on. I wonder what caused the line-breaks. To the best of my recollection it has always generated one line per statement, no matter the length of said line.
-
A colleague today asked me to show him the database structure for an application I wrote a couple of years ago, for maintenance purposes. I directed him to the database diagram I had helpfully created in SQL Server. Sadly, upon opening it, it became apparent that someone had for some unknown reason decided to remove all the relationships between all the tables, for no obvious reason. I despair sometimes.
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
Maybe you did it one night after taking some Adderol... ;P
#SupportHeForShe Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun
-
Rob Grainger wrote:
for some unknown reason
It's always the same reason: Some dimwit tries to insert or delete something and bounces off these foreign key constraints. Instead of adapting the application logic to take the constraints into account, the harebrain throws the constraints (and the database's integrity) out the window. And what will they say when you ask them which part of 'referential integrity' they did not understand? (Offended whine): "But it works (*)!" (*) In there limited little world that means that the error message is gone, nothing more.
I have lived with several Zen masters - all of them were cats.
Sad but true. I used to support such db at the start of my career
-
Thank you for the heads up. Haven't run into that before, at least that I've ever noticed, but will start to check from now on. I wonder what caused the line-breaks. To the best of my recollection it has always generated one line per statement, no matter the length of said line.
Yeah, that was the most weird thing I've seen in sql.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
A colleague today asked me to show him the database structure for an application I wrote a couple of years ago, for maintenance purposes. I directed him to the database diagram I had helpfully created in SQL Server. Sadly, upon opening it, it became apparent that someone had for some unknown reason decided to remove all the relationships between all the tables, for no obvious reason. I despair sometimes.
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.
was probably having issues removing a row ! HAAAAAA !
-
Be careful of the "Script To..." option. Pay Attention to What "Script...To" Generates[^]
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013Last year I finally got around to writing a utility that uses SMO with exactly the settings I insist on for generating scripts. Things are so much more stable now.
-
Trust me, it could be worse. My company sells a very large application using a SQL Server database. There are over 1500 tables in the database. You can count the number of defined FK relations on one hand and I suspect those were added by mistake. I have brought this up several times and it's always the same answer. We don't need no stinking FK relations in the database - the application code handles all of that. Of course, the poor support people constantly have to deal with application issues caused by orphaned data, etc.
txmrm wrote:
with application issues caused by orphaned data
Then they're not testing properly. A production app should not rely on constraints.
-
I've experienced something like that once. For no reason, quite a few FK's had disappeared... I'm pretty certain no one on the team did it because we all knew the value of FK's. To this day I've seen it once and still can't explain it. My guess is that some external tool (comparer? EF? modeller?) removed them for some reason.
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
I've seen this too, and know the reason. It's not a good reason, but it's a reason. I'd designed and built a database with full referential integrity. A team of programmers started to build a system around it. Every time their code violated the rules, guess what? An exception was triggered! As intended. Imagine that. It came to time to roll out the project. This meant deploying a fresh copy of the database to a production server. I used the same scripts that I'd written to deploy the development copy. This resulted in a herd of coders arriving at my desk, all red in the face, and demanding that I use the copy on the development machine. It seems that between them, their understanding of error handling consisted of "on error goto", and they'd been so overwhelmed by the exceptions that the RI in the database caused, they'd removed all of it from the database. Seems my idiot boss saw no harm in giving them admin rights on the database server. Thankfully, he took my side when I explained to him why the project was going to be delayed! So, if you're surrounded by idiots, then there's a reason why the RI was removed. It's not a good reason, but it's certainly a reason. Namely, you're surrounded by idiots.
-
I've seen this too, and know the reason. It's not a good reason, but it's a reason. I'd designed and built a database with full referential integrity. A team of programmers started to build a system around it. Every time their code violated the rules, guess what? An exception was triggered! As intended. Imagine that. It came to time to roll out the project. This meant deploying a fresh copy of the database to a production server. I used the same scripts that I'd written to deploy the development copy. This resulted in a herd of coders arriving at my desk, all red in the face, and demanding that I use the copy on the development machine. It seems that between them, their understanding of error handling consisted of "on error goto", and they'd been so overwhelmed by the exceptions that the RI in the database caused, they'd removed all of it from the database. Seems my idiot boss saw no harm in giving them admin rights on the database server. Thankfully, he took my side when I explained to him why the project was going to be delayed! So, if you're surrounded by idiots, then there's a reason why the RI was removed. It's not a good reason, but it's certainly a reason. Namely, you're surrounded by idiots.
How could such software go into production? It's not correct by definition :wtf: No (acceptance) testing?
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
-
How could such software go into production? It's not correct by definition :wtf: No (acceptance) testing?
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
-
That kind of stuff was reserved for basic training or special occasions when you intended to hold a monologue to someone (which then usually ended with 'Dismissed', meaning 'get out of my sight').
I have lived with several Zen masters - all of them were cats.
Ah, the good, old high volume one-way discussion.
