mySQL 中的外键和 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2802545/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Foreign keys and NULL in mySQL
提问by Industrial
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:
我可以在我的值表 (value) 中有一个列作为 knownValues 表的外键引用，并在需要时让它为 NULL，就像在示例中一样：
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& knownValuesare of course referenced into the typestable.
注：表values&knownValues中的类型当然是在表中引用的types。
回答by Salman A
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 是完全可以接受的。处理外键中的 NULL 很棘手，但这并不意味着您将此类列更改为 NOT NULL 并在参考表中插入虚拟（“N/A”、“未知”、“无值”等）记录。
Using NULLs in foreign keys often requires you to use LEFT/RIGHT JOIN instead of INNER JOIN.
在外键中使用 NULL 通常需要使用 LEFT/RIGHT JOIN 而不是 INNER JOIN。
回答by nvogel
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.
尽管您可以使外键列可以为空，但我建议设计没有可为空外键的表通常会更好。空值总是会导致某些歧义和不正确的结果，但如果预期相关列受到某些约束，那将是双重问题。
回答by Raj More
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.
这是一个 1 对零对多的关系。我在 SQL Server 上使用过很多次。我相信用 MySQL 也可以做到这一点。
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.
由于与数据聚合相关的问题，我更喜欢在我的数据库中避免 NULL，因此，根据我的设计，我在查找表中放置了一个 UNKNOWN 行。
回答by Brian Hooper
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.
是的，很可能在您的外键约束列中有一个 NULL。我刚试过。请记住，如果您没有使用 InnoDB 存储引擎，无论如何您的外键约束都将被忽略。
回答by Prabhu M
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.
当然，外键也有可能为NULL，不过你不用担心，我希望你可以使用InnoDB作为数据库引擎来管理Key约束。对于这种情况，我建议使用 Left Join 或 Right Join 从 DB 和 Group By 获取行以避免重复。请不要使用内部连接。
