Can MySQL Foreign Keys Allow NULL Values?

Does MySQL foreign keys allow NULL values?

Enforcing data integrity is critical when designing database tables. References to columns of other tables through foreign keys play a key role in maintaining these connections. A common question is: Do table columns with foreign keys allow NULL values?

In MySQL using the InnoDB table type, the answer is yes. You can declare foreign key columns as nullable. This behavior allows you to insert new rows with NULL values ​​in foreign key columns, but enforces constraints when entering non-NULL values.

To illustrate this, consider the following example:

CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id));
CREATE TABLE child (
  id INT NULL,
  parent_id INT NULL,
  FOREIGN KEY (parent_id) REFERENCES parent(id)
);

Now, let’s insert a row into the “child” table where “parent_id” is NULL:

INSERT INTO child (id, parent_id) VALUES (1, NULL);
-- 查询成功，影响 1 行 (0.01 秒)

This insert operation succeeded because we specified a NULL value for the "parent_id" column. However, trying to insert a non-NULL value that does not exist in the "parent" table will result in an error because the foreign key constraint is enforced:

INSERT INTO child (id, parent_id) VALUES (2, 1);

-- 错误 1452 (23000)：无法添加或更新子行：外键约束失败 (`t/child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

This behavior provides flexibility and control over data integrity. You can allow temporary NULL values ​​during data population while still enforcing foreign key constraints later. By carefully managing NULL constraints on foreign key columns, you can ensure the accuracy and consistency of your data.

The above is the detailed content of Can MySQL Foreign Keys Allow NULL Values?. For more information, please follow other related articles on the PHP Chinese website!