MySQL 带有外键的表列可以为 NULL 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2366854/
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
Can table columns with a Foreign Key be NULL?
提问by bender
I have a table which has several ID columns to other tables.
我有一个表，其中有几个 ID 列到其他表。
I want a foreign key to force integrity onlyif I put data in there. If I do an update at a later time to populate that column, then it should also check the constraint.
只有当我将数据放入那里时，我才想要一个外键来强制完整性。如果我稍后进行更新以填充该列，那么它还应该检查约束。
(This is likely database server dependant, I'm using MySQL & InnoDB table type)
（这可能取决于数据库服务器，我使用的是 MySQL 和 InnoDB 表类型）
I believe this is a reasonable expectation, but correct me if I am wrong.
我相信这是一个合理的期望，但如果我错了，请纠正我。
回答by Daniel Vassallo
Yes, you can enforce the constraint only when the value is not NULL. This can be easily tested with the following example:
是的，您只能在值不为 NULL 时强制执行约束。这可以通过以下示例轻松测试：
CREATE DATABASE t;
USE t;
CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT NULL,
parent_id INT NULL,
FOREIGN KEY (parent_id) REFERENCES parent(id)
) ENGINE=INNODB;
INSERT INTO child (id, parent_id) VALUES (1, NULL);
-- Query OK, 1 row affected (0.01 sec)
INSERT INTO child (id, parent_id) VALUES (2, 1);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key
-- constraint fails (`t/child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY
-- (`parent_id`) REFERENCES `parent` (`id`))
The first insert will pass because we insert a NULL in the parent_id. The second insert fails because of the foreign key constraint, since we tried to insert a value that does not exist in the parenttable.
第一次插入会通过，因为我们在parent_id. 由于外键约束，第二次插入失败，因为我们试图插入parent表中不存在的值。
回答by Backslider
I found that when inserting, the null column values had to be specifically declared as NULL, otherwise I would get a constraint violation error (as opposed to an empty string).
我发现在插入时，必须将空列值明确声明为 NULL，否则我会收到约束违规错误（与空字符串相反）。
回答by davidtbernal
Yes, that will work as you expect it to. Unfortunately, I seem to be having trouble to find an explicit statement of this in the MySQL manual.
是的，这会按您的预期工作。不幸的是，我似乎很难在MySQL 手册中找到对此的明确声明。
Foreign keys mean the value must exist in the other table. NULLrefers to the absence of value, so when you set a column to NULL, it wouldn't make sense to try to enforce constraints on that.
外键意味着该值必须存在于另一个表中。NULL是指没有值，因此当您将列设置为 NULL 时，尝试对其施加约束是没有意义的。
回答by Toby Crain
Yes, the value can be NULL, but you must be explicit. I have experienced this same situation before, and it's easy to forget WHY this happens, and so it takes a little bit to remember what needs to be done.
是的，该值可以为 NULL，但您必须明确。我以前也遇到过同样的情况，很容易忘记为什么会发生这种情况，因此需要一点时间记住需要做什么。
If the data submitted is cast or interpreted as an empty string, it will fail. However, by explicitly setting the value to NULL when INSERTING or UPDATING, you're good to go.
如果提交的数据被强制转换或解释为空字符串，它将失败。但是，通过在 INSERTING 或 UPDATING 时将值显式设置为 NULL，您就可以开始了。
But this is the fun of programming, isn't it? Creating our own problems and then fixing them! Cheers!
但这就是编程的乐趣，不是吗？创建我们自己的问题，然后解决它们！干杯!
回答by MrFabulous
The above works but this does not. Note the ON DELETE CASCADE
以上有效，但这不起作用。注意 ON DELETE CASCADE
CREATE DATABASE t;
USE t;
CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT NULL,
parent_id INT NULL,
FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
) ENGINE=INNODB;
INSERT INTO child (id, parent_id) VALUES (1, NULL);
-- Query OK, 1 row affected (0.01 sec)
回答by Wildhammer
Another way around this would be to insert a DEFAULT element in the other table. For example, any reference to uuid=00000000-0000-0000-0000-000000000000 on the other table would indicate no action. You also need to set all the values for that id to be "neutral", e.g. 0, empty string, null in order to not affect your code logic.
另一种解决方法是在另一个表中插入一个 DEFAULT 元素。例如，对另一个表中 uuid=00000000-0000-0000-0000-000000000000 的任何引用都表示没有操作。您还需要将该 id 的所有值设置为“中性”，例如 0、空字符串、null，以免影响您的代码逻辑。
回答by Shams Reza
I also stuck on this issue. But I solved simply by defining the foreign key as unsigned integer.
Find the below example-
我也卡在这个问题上。但我只是通过将外键定义为unsigned integer. 找到下面的例子-
CREATE TABLE parent (
id int(10) UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
id int(10) UNSIGNED NOT NULL,
parent_id int(10) UNSIGNED DEFAULT NULL,
FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
) ENGINE=INNODB;
