Should I use foreign keys every related tables or shouldn't I? If I should use, why?
Asked
Active
Viewed 8,395 times
8
-
possible duplicate of [Are foreign keys really necessary in a database design?](http://stackoverflow.com/questions/18717/are-foreign-keys-really-necessary-in-a-database-design) – cHao Jan 06 '12 at 07:31
-
Does this answer your question? [Are foreign keys really necessary in a database design?](https://stackoverflow.com/questions/18717/are-foreign-keys-really-necessary-in-a-database-design) – EdChum Aug 07 '20 at 07:52
6 Answers
12
You should. There are three main points:
- Depending on your DB system, you could get better performance
- Foreign keys ensure data integrity, e.g. can help to avoid orphan records etc
- They an explicit way to document your DB structure, which can be used by tools for visualization, code generation etc.
Landei
- 54,104
- 13
- 100
- 195
10
Using Foreign keys is one of the basis (if not the only) , the main concept of relational databases. You off course should use foreign keys where needed. Because this helps to:
- Make sure of the data validation and integrity
- Save lots of extra space
The first one means instead of adding a value manually to a field that is going to be repeated for other records you just choose from what you have in the related, the primary key table. If you try to enter something that does not exist as a primary key in the other table you'll be rejected (In some databases you can tweak this behavior though).
The second one means You don't have to write the "United States of America" each time, which would take a lot more space than just writing the ID of the "United States of America".
Mikayil Abdullayev
- 12,117
- 26
- 122
- 206
8
"All related tables" is not always clear, so it's not an obvious situation. There can be tables that have a common column but may never see each other.
It's handy, however, to prevent errors that slip past your primary defenses and allow data to be entered that's not easily traceable.
They don't help make queries more efficient if you have the proper indexes in place, and a good application will filter input enough that they should never be needed. But mistakes happen, and they are a cheap line of defense.
If you're just getting comfortable with designing databases, they aren't something to spend a lot of time worrying about and refining in great detail, once you have the basic parent/child relationships in place.
Here's some background--
6
Yes. But on moderate levels. This helps when you are querying data. It helps in indexing your data, so faster queries. Also it helps in maintaining the relationships between entities.
Amar Palsapure
- 9,590
- 1
- 27
- 46
5
Yes, you should.Foreign keys are just constrains which helps you to make relationships and be sure that you have correct information in your database.You should use them to prevent incorrect data entry from whatsoever.
shift66
- 11,760
- 13
- 50
- 83
4
There's actually a growing move away from foreign key based relational databases at the moment. Document Database systems such a MongoDB are becoming much more popular. This is largely because the world is becoming much more distributed with the cloud.
This means that sometime not reasonable or performant to assume immediate data consistency.
Have a read on NoSQL based database, MongoDB CouchDB and eventual consistency if you are interested.
Its kinda a weird thing for our relational trained minds to understand but a lot of large scale web solutions need to sacrifice consistency for availability or partition tolerance.
-
Going from Mongo to Postgres really screws with my mind as well. I'm not used to this whole id thing and having to "pre think" my database to handle it properly... – VocoJax Aug 22 '18 at 19:02