2

I'm confused about something, time and again i've seen developers including team leaders tell me that using foreign key constraints on their DB is bad practice, that it adds "logic to the database" but that goes against everything i've ever learned about SQL so far...

I need someone to tell me if i'm wrong for thinking that foreign key constraints are a major benefit or if i'm totally wrong and if so why. It just makes sense to me that in order to ensure data integrity you'd refuse any data that doesn't make sense being there, i'm sorry if this is a duplicate question, but i'm not really finding any definitive answers i'm happy with.

JonnySerra
  • 1,044
  • 3
  • 13
  • 27
  • 1
    I flagged this question, because it will result in opinion based answers. And the usage of foreign keys, is up to one self, whether they are useful or not, is based on situation. To be clear, I don't think it is a bad question at all... just the answers will be bad. –  Dec 03 '16 at 18:31
  • 2
    [What's wrong with foreign keys?](http://stackoverflow.com/questions/83147/whats-wrong-with-foreign-keys) – Solarflare Dec 03 '16 at 18:32
  • I'm voting to close this question as off-topic because this is a beef. Sure, we share it. – Drew Dec 03 '16 at 18:33
  • @juergend Sometimes they're not really an issue if you have other ways of ensuring data integrity or the consequences of an unlinked record are minimal. – tadman Dec 03 '16 at 18:49
  • I'd love to see what those assurances are. – Drew Dec 03 '16 at 19:01
  • @tadman what would that other way of ensuring integrity be? I'm interested, enlighten us. – N.B. Dec 03 '16 at 19:09
  • @N.B. For a system where data integrity is paramount you probably want to use foreign keys. For systems where there's a willingness to trade absolute integrity for performance or simplicity they're not strictly necessary. A message board does not need foreign keys. Anything financial probably does. There's a huge grey zone in the middle. You can have good-enough integrity by using transactions to make changes atomic so the chance of a randomly dropped link is very low. – tadman Dec 03 '16 at 19:15
  • @N.B. Additionally, when operating at scale and distributing data across N databases that aren't necessarily clustered together you can't use foreign keys anyway. They're only applicable in some situations. In those cases you need to run automated sanity checks to see if there's anything inconsistent or missing and repair it if possible, plus alert if there's an unusually high level of anomalies found. – tadman Dec 03 '16 at 19:16
  • 1
    So the answer so far is you can't without FK's. There is no Referential Integrity without FK's. The `NDB` storage engine handles RI across clusters. – Drew Dec 03 '16 at 19:17
  • @Hallur it might result in opinion based answers, but what's the point of SO if we can't debate concepts like Database design? Where do you go to discuss best practices and the like? That's one rule of SO's Community i never quite understood, opinions are a good thing, you make up your own mind afterwards but you should hear what other people think first. – JonnySerra Dec 03 '16 at 19:29
  • And don't forget, you either have data integrity or you don't. There is no such thing as pretty good integrity or all that jazz above. Don't fool yourselves. – Drew Dec 03 '16 at 19:32
  • Thanks everyone, i think everyone kinda agrees that when you need to ensure integrity in the DB you need Foreign Key constraints, it does induce performance hits so you need to consider if your app really has relationships that would be benefited from these constraints. In my case they definitely do, so i'll keep using them against my TL's instruction. I hope this can help other people in the future :) Thanks again to everyone involved in the discussion – JonnySerra Dec 03 '16 at 19:33
  • @JonnySerra I somewhat agree with that... but I think the purpose of SO is somewhat more about solving problems, than discussing things. Besides, there are other sites in the stack exchange where this question fits in better... The purpose of not allowing opinion based questions may be because, in a scientific world, opinion does not matter, if it is not based on actual scientific data. –  Dec 03 '16 at 19:38
  • @tadman ok, so the answer is - you can't without foreign keys. – N.B. Dec 03 '16 at 20:03
  • @Drew At some point NDB won't cut it, you can't put 100+ servers in a cluster like that and expect it to work reliably. Foreign keys don't work in all situations. – tadman Dec 03 '16 at 20:19
  • @tadman all best practices lean toward referential integrity enforced by the db. If you want to write clean up scripts that run at night to point out where your data is wrong, there is nothing standing in your way. The rest of us (or many of us) have a different view on the way we work as data architects. – Drew Dec 03 '16 at 20:24
  • @drew I'm specifically talking about exceptions to the rule, not best practices. – tadman Dec 03 '16 at 20:56

1 Answers1

0

thinking that foreign key constraints are a major benefit

That statement is bit confusing to me cause whether you will create a FK constraint between tables depends on whether those table(s) contains/maintain any relationship between them or not; like Students has courses (OR) Employees works under department

If those table can exist individually (stand alone) then why would you create a FOREIGN KEY constraint at all.

Per your comment: Then what's the reason for not using foreign keys. Ask your lead to give the explanation behind not using FK's instead just saying they are bad. If your lead says that they don't want to enforce foreign key constraint then suggest them to go for NOSQL database like MongoDB or Cassandra and not use Relational Database

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • yes they do, of course, in my particular case it's a Crons platform where i have Campaigns and Crons associated with them. So one Campaign can have multiple Crons etc.. and i used FK constraints to ensure that no invalid ID's can be inserted at any point, and to ensure that no Campaign can be deleted while there's any Cron that references it. – JonnySerra Dec 03 '16 at 18:15
  • @JonnySerra, see edit in answer if that helps. – Rahul Dec 03 '16 at 18:25
  • @downvoter: care to explain the reason for downvote – Rahul Dec 03 '16 at 18:26
  • You actually did not answer the question, did you? – juergen d Dec 03 '16 at 18:27
  • @juergend, I believe I did. – Rahul Dec 03 '16 at 18:30
  • can you clarify "If those table can exist individually (stand alone) then why would you create a FOREIGN KEY constraint at all." ... I didn't dv you but that seems like a wild statement. – Drew Dec 03 '16 at 18:46
  • @Drew, *seems like a wild statement.* why? what I mean to say is: what if you have a table which doesn't hold (or not part of) any relationship. Will you still go for maintaining integrity constraint for it? – Rahul Dec 03 '16 at 19:33
  • Give an example perhaps so it is clear. In short if you want to assure data integrity and forbid updates or inserts that are known to be faulty....you know the drill, use FK's. But if you have some hypothetical you can show, please do. – Drew Dec 03 '16 at 19:37