Can Foreign Key Columns Allow NULL Values While Maintaining Data Integrity?

Can foreign key columns be empty?

Can a table column containing a foreign key be set to NULL? This is particularly important in situations where data integrity must be maintained but the foreign key column may not always have a value.

Answer: Conditional constraint enforcement

Yes, it is possible to enforce foreign key constraints only if the value is not NULL. This ensures data integrity while allowing null values ​​in foreign key columns.

To illustrate this, consider the following MySQL sample code:

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

Insert operation with NULL parent_id will succeed:

<code class="language-sql">INSERT INTO child (id, parent_id) VALUES (1, NULL);</code>

However, inserting a value in parent_id that does not exist in the parent table will cause a foreign key constraint violation:

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

This indicates that foreign key constraints are only enforced if the parent_id column contains a valid reference, effectively allowing null values ​​to avoid the constraint.

The above is the detailed content of Can Foreign Key Columns Allow NULL Values While Maintaining Data Integrity?. For more information, please follow other related articles on the PHP Chinese website!