外键和空键在mySQL中

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

表:knownValues

id    Type     name
0     2        banana

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

注意:表值和knownValues中的类型当然被引用到类型表中。

5 个解决方案

#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.

外键中的null是完全可以接受的。处理外键中的空值是很棘手的，但这并不意味着要将此类列更改为非NULL，并在引用表中插入假记录(“N/A”、“Unknown”、“No Value”等)。

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

在外键中使用null通常需要使用左/右连接而不是内连接。

#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.

尽管您可以使外键列为空，但我建议在没有空键的情况下设计表通常更好。Nulls总是会导致某些含糊不清和不正确的结果，但如果所讨论的列预期受到某些约束，那么这将是一个双重的问题。

#3

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到0到多的关系。我在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，因此根据我的设计，我在查找表中放置了一个未知行。

#4

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存储引擎，您的外键约束将被忽略。

#5

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.

当然，有可能ti在外键中有一个空值，但是您不必为此担心，我希望您可以使用InnoDB作为数据库引擎来管理键约束。对于这种情况，我建议使用左连接或右连接从DB获取行，而Group By可用于避免重复。请不要使用内连接。

#1