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.

Salman A · Accepted Answer · 2014-06-11T08:33:57.183

32

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.

edited Jun 11 '14 at 08:33

answered May 10 '10 at 12:58

Salman A

262,204
82
430
521

4

The use of INNER join or some kind of OUTER join depends on the results you are looking for. There are times when an INNER join gives exactly the desired answer. – Walter Mitty May 10 '10 at 13:44
1

Yes, and in cases when you have rows with NULLs in FK, you won't find *all* rows with an inner join; but with an outer join. – Salman A May 10 '10 at 13:54
3

any examples? This is not working for me using InnoDB Storage Engine - MySQL – dev Jun 10 '14 at 18:13

score 7 · Answer 2 · answered May 10 '10 at 12:45

7

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.

answered May 10 '10 at 12:45

nvogel

24,981
1
44
82

1

Hi David, so how would you do it? An additional table for free-text-values only? – Industrial May 10 '10 at 12:50
2

Yes, putting it into another table is the obvious way to do it. If you represent facts in tables with the right set of attributes then you won't need to use nulls for attributes that don't apply. Only add nulls to the model where there is some special advantage to doing so or where some software limitation forces you to. – nvogel May 10 '10 at 13:13

score 4 · Answer 3 · answered May 10 '10 at 12:36

4

This is a 1 to zero-to-many relationship. I have used this many times with SQL Server. I believe it is possible to do this with MySQL as well.

I prefer to avoid NULLs in my databases because of issues related to data aggregation so, depending on my design, I put an UNKNOWN row in the lookup table.

answered May 10 '10 at 12:36

Raj More

47,048
33
131
198

4

Hi Raj! How do you mean by "UNKNOWN row"? – Industrial May 10 '10 at 12:59

score 1 · Answer 4 · answered May 10 '10 at 12:45

1

Yes it is quite possible to have a NULL in your foreign-key-constrained column. I just tried it. Bear in mind that if you are not using the InnoDB storage engine, your foreign key constraints will be ignored anyway.

answered May 10 '10 at 12:45

Brian Hooper

21,544
24
88
139

score 1 · Answer 5 · answered May 10 '10 at 13:13

Of course, there is a possibilities ti have a NULL values in foreign key, but for that you don't get worry about this, I hope you may used InnoDB as database engine to manage the Key constraints. For this case i suggest to use Left Join or Right Join to get rows from DB and Group By can be used for avoid duplication. Please do not use Inner Join.

Foreign keys and NULL in mySQL

5 Answers5

Linked