Can Foreign Key Columns Accept NULL Values in MySQL's InnoDB?

Foreign keys and NULL values ​​in database table columns

In database design, foreign key constraints are often used to ensure data integrity by enforcing relationships between tables. However, the question arises: are table columns with foreign keys allowed to hold NULL values?

As shown in the provided question, there may be situations where foreign key constraints need to be enforced only when the value is explicitly set. This allows records with NULL values ​​to be initially inserted in foreign key columns while ensuring data integrity after the values ​​are populated.

In MySQL and InnoDB table types, it is indeed possible to have non-NULL foreign keys that allow NULL values. The following example demonstrates this:

CREATE DATABASE t;
USE t;

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (id INT NULL,
                    parent_id INT NULL,
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
) ENGINE=INNODB;

Allow records with parent_id to be NULL to be inserted into the child table:

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

However, trying to insert a record whose parent_id value does not exist in the parent table will result in a foreign key constraint violation:

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

-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key
-- constraint fails (`t/child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY
-- (`parent_id`) REFERENCES `parent` (`id`))

This behavior confirms that MySQL and InnoDB allow NULL values ​​for foreign key columns and only enforce constraints when a non-NULL value is provided. This is consistent with the expectation that foreign key constraints should be enforced when the foreign key column is populated with data, while allowing initial NULL values ​​in the foreign key column.

The above is the detailed content of Can Foreign Key Columns Accept NULL Values in MySQL's InnoDB?. For more information, please follow other related articles on the PHP Chinese website!