外键和NULL在mySQL中 [英] Foreign keys and NULL in mySQL
问题描述
表:值
产品类型值freevalue
0 1 NULL 100
1 2 NULL 25
3 3 1 NULL
表:类型
id名称前缀
pre>
0长度厘米
1重量kg
2水果NULL
表:knownValues
id类型名称
0 2香蕉
注意:表中的类型
值
&knownValues
当然被引用到类型
表中。解决方案外键中的NULL完全可以接受。处理外键中的NULL是棘手的,但这并不意味着您将这些列更改为NOT NULL,并在引用表中插入dummy(N / A,Unknown,No Value等)记录。
在外键中使用NULL通常需要使用LEFT / RIGHT JOIN而不是INNER JOIN。
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 thetypes
table.解决方案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.
这篇关于外键和NULL在mySQL中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!