mySQL中的外键和NULL

2022-01-20 00:00:00 sql foreign-keys mysql database-design database-relations

我可以在我的 values 表(值)中有一个列作为 knownValues 表的外键引用，并在需要时让它为 NULL，如示例中所示:

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:

表格:值

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

表格:类型

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

表:已知值

id    Type     name
0     2        banana

注意:表中的类型 values &knownValues 当然被引用到 types 表中.

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

推荐答案

外键中的 NULL 是完全可以接受的.处理外键中的 NULL 很棘手，但这并不意味着您将此类列更改为 NOT NULL 并在参考表中插入虚拟(N/A"、Unknown"、No Value"等)记录.

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.

在外键中使用 NULL 通常需要您使用 LEFT/RIGHT JOIN 而不是 INNER JOIN.

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

相关文章