Foreign keys and NULL in mySQL

Question

Can I have a column in my values table (value) referenced as a foreign key to knownValues table, and let it be NULL whenever needed, like in the example:

Table: values

 product     type     value     freevalue  0           1        NULL      100  1           2        NULL      25  3           3        1         NULL 

Table: types

 id    name     prefix  0     length   cm  1     weight   kg  2     fruit    NULL 

Table: knownValues

id    Type     name 0     2        banana  

Note: The types in the table values & knownValues are of course referenced into the types table.

Answer 1

NULLs in foreign keys are perfectly acceptable. Dealing with NULLs in foreign keys is tricky but that does not mean that you change such columns to NOT NULL and insert dummy ("N/A", "Unknown", "No Value" etc) records in your reference tables.

Using NULLs in foreign keys often requires you to use LEFT/RIGHT JOIN instead of INNER JOIN.

Answer 2

Although you can make foreign key columns nullable I would suggest that it's generally better to design tables without nullable foreign keys. Nulls invariably lead to certain ambiguities and incorrect results but that's doubly a problem if the columns in question are expected to be subject to some constraint.