1

I have a users table which has user_id as primary key and it is used in other 40 to 50 tables(approx.). Is it necessary to define user_id column as foreign key in each and every table where user_id is being used?

Improve this question
2
  • 1
    Define "necessary" in that context. Necessary to achieve what? At any rate it's very recommendable...
    – sticky bit
    Commented Dec 24, 2018 at 13:56
  • 2
    The lessons of history are clear: if you don't enforce a foreign key you will eventually have orphaned records in the child tables.
    – APC
    Commented Dec 24, 2018 at 14:08

2 Answers 2

Reset to default
3

It depends. RI/FKs were introduced in Oracle in v7, so databases most definitely existed without them, and some still do. But it is common to have these defined and enabled as you basically get this checking 'for free'.

There may be some specialist cases where you might prefer not to use them. I worked on one low latency system where speed was everything, and the order in which parent/child data was posted to the database from the application management layer could not be guaranteed. In this case we found it faster to build the integrity rules in the apps management layer. As I said, a specialist case, seldom seen elsewhere, will make the purists spit blood but it worked very well and has been a successful system for over 10 years.

However, I do stress again, that FK/RI are good overall, and I would reverse you question to say "When should I consider not having FK?".

In my experience, you'll most commonly find these missing out of sheer laziness on the part of developers.

Improve this answer
1
  • 1
    and the order in which parent/child data was posted to the database from the application management layer could not be guaranteed. ... that sounds like a real problem that should be addressed, or at least a symptom of some other problem.
    – Clockwork-Muse
    Commented Dec 24, 2018 at 22:05
1

It is not required, but you want to maintain data integrity, it is necessary. Many databases, use it to find data faster. It is a good practice.

Improve this answer
3
  • 1
    If a database is using a FK relationship to "find data faster", that's almost certainly a side-effect of the way the keys are usually enforced - with (one or more) indices. Checking and maintaining data based on such keys will almost always have an update-time (as opposed to query-time) performance penalty.
    – Clockwork-Muse
    Commented Dec 24, 2018 at 22:03
  • FK fields are indexed, so to find, they are faster than not indexed, but to insert and delete, the database verify the constraint, for theses operations the data base being slower.
    – Roberto Góes
    Commented Dec 24, 2018 at 22:14
  • 1
    Whether FK fields (or any other key fields) are indexed, or such indices are queried, is an implementation detail - and is in no way guaranteed by the spec. It's just one of the easiest ways to implement the necessary checks with tables of any significant size (so database will often create a hidden index for the necessary key, if you don't explicitly define one). If, on the other hand, I have a table of size 1, the database is going to read the entire thing into memory, and never consult any indices for much of anything.
    – Clockwork-Muse
    Commented Dec 24, 2018 at 22:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.