MySQL Foreign Key Constraint - Integer Column

问题:

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?

回答1:

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)

回答2:

However, think long and hard about allowing that FK column to be null. Null means you can have a child record without a parent. Is this really a valid business case? Can you give an example where this is valid?

Also, a null FK (or any null column) means you are now making assumptions about what the null means. How do you tell if the field really is not supposed to have a value, vs someone forgetting to put a value in? You could implement Not Null logic in the app and bypass the issue that way, though this still involves risks. Better minds than mine have said that allowing nulls causes a less stable database design. YMMV. Perhaps consider a default value like -1 that still forces a not null FK and looks up to a dummy record.

回答3:

Perhaps allow nulls in your foreign key column and set the default to null.