Foreign key(s)

The quick question would be: yes/no/perhaps ?

The long version:
Should foreign keys be used and if so when?
or is it more a ‘it-depends-on-…’ thingie?

For what i’ve read about it so far it’s more of a consistency thing and not a must have and it creates some overhead and that’s why bigger companies (with big databases) put the ‘checking’ into the application, not the db.

i don’t know where you got the information that led to this conclusion, but there is a word for this type of conclusion, and that word is wrong

yes, you should always declare foreign keys (except for myisam tables, which you should use only rarely anyway)

foreign keys are definitely a “must have” because they ensure data integrity

putting the checking into the application means (1) more overhead in the application, and (2) a reliance on the application for all data entry, i.e. you cannot dare to load data into the database any other way because there are no data integrity controls

i got it right from the horses mouth aka MySQL manual:

Do keep in mind that these benefits come at the cost of additional overhead for the database server to perform the necessary checks. Additional checking by the server affects performance, which for some applications may be sufficiently undesirable as to be avoided if possible. (Some major commercial applications have coded the foreign key logic at the application level for this reason.)

But that’s why i’m asking the real expert here :wink:

got it, thank you

so now i just have to check why some foreign keys can be set and some not (at my tables)
Pretty likely i’ll be back :rolleyes:

the mysql manual, as always, is a good source of information

note that it says “some major commercial applications…” whereas you said " bigger companies (with big databases)…"

significant difference :slight_smile: