Do Foreign Key Constraints in MySQL InnoDB Allow NULL Values?

Foreign key constraints and NULL values ​​in MySQL InnoDB tables

Question:

Can foreign key constraints in a database table still take effect when the column contains NULL values? Especially in the context of MySQL and InnoDB tables, what is the behavior of foreign key constraints when a NULL value is encountered?

Answer:

Yes, foreign key constraints can only be enforced if the foreign key column value is not NULL. This allows flexibility in data management and ensures data integrity.

In MySQL, when a table column with a foreign key constraint contains a NULL value, the constraint is not checked. This means that a parent-child relationship is not enforced for rows with NULL in the foreign key column.

However, when a non-NULL value is inserted into a foreign key column, the database checks whether the value exists in the referenced table. If the value does not exist, an error is thrown and the insert operation fails, preventing referential integrity.

Example:

Consider the parent and child tables with the following structure:

<code class="language-sql">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));</code>

The following query illustrates the behavior of foreign key constraints with NULL values:

<code class="language-sql">INSERT INTO child (id, parent_id) VALUES (1, NULL);
-- 查询成功，因为 parent_id 为 NULL。

INSERT INTO child (id, parent_id) VALUES (2, 10);
-- 查询失败，因为没有 id 为 10 的 parent 行。</code>

This indicates that foreign key constraints are only enforced if the foreign key column contains a non-NULL value.

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