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)