MySQL外键约束 - 整数列 [英] MySQL Foreign Key Constraint - Integer Column

问题描述

我有一个整数列，我想添加一个外键约束。唯一的问题是，如果该列没有/需要一个值，默认情况下，MySQL放入一个0值。这显然打破了外键约束，因为在主表中没有PK记录0。

我该如何克服这个问题？

解决方案

您可能希望将外键设置为接受 NULL 值，并使用 NULL ，而不是 0 值。

从概念上讲， NULL 表示缺少未知值。如果你的行没有/需要的值，我相信一个 NULL 完全适合。

是的， NULL 值不会破坏你的外键约束。

让我们来构建一个基本的例子：

$ p $ CREATE TABLE parents（
id int PRIMARY KEY，
value int
）ENGINE = INNODB;

CREATE TABLE children（
id int PRIMARY KEY，
parent_id int，
FOREIGN KEY（parent_id）REFERENCES parent（id）
）ENGINE = INNODB ;


然后：

  INSERT INTO parents VALUES（1，100）; 
 Query OK，1 row affected（0.00 sec）
 
 INSERT INTO children VALUES（1，1）; 
 Query OK，1 row affected（0.00 sec）
 
 INSERT INTO children VALUES（2，0）; 
错误1452（23000）：外键约束失败
 
 INSERT INTO children VALUES（2，NULL）; 
 Query OK，1 row affected（0.00 sec）
 
 SELECT * FROM children; 
 + ---- + ----------- + 
 | id | parent_id | 
 + ---- + ----------- + 
 | 1 | 1 | 
 | 2 | NULL | 
 + ---- + ----------- + 
 2行（0.01秒）
  

I have an integer column which I would like to add a foreign key constraint to. Only problem is if that column does not have/need a value, by default MySQL puts in a '0' value. This obviously breaks the foreign key constraint as there is no record in the primary table with a PK of 0.

How can I overcome this problem?

解决方案

You may want to set your foreign key to accept NULL values, and use a NULL instead of a 0 value.

Conceptually, NULL means a missing unknown value. If your row does "not have/need the value", I believe a NULL fits in perfectly.

And yes, a NULL value would not break your foreign key constraint.

Let's build a basic example:

CREATE TABLE parents (
   id      int PRIMARY KEY, 
   value   int
) ENGINE = INNODB;

CREATE TABLE children (
   id         int PRIMARY KEY, 
   parent_id  int,
   FOREIGN KEY (parent_id) REFERENCES parent (id)
) ENGINE = INNODB;

Then:

INSERT INTO parents VALUES (1, 100);
Query OK, 1 row affected (0.00 sec)

INSERT INTO children VALUES (1, 1);
Query OK, 1 row affected (0.00 sec)

INSERT INTO children VALUES (2, 0);
ERROR 1452 (23000): A foreign key constraint fails

INSERT INTO children VALUES (2, NULL);
Query OK, 1 row affected (0.00 sec)

SELECT * FROM children;
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |         1 |
|  2 |      NULL |
+----+-----------+
2 rows in set (0.01 sec)

这篇关于MySQL外键约束 - 整数列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！