I have a table which has several ID columns to other tables.
I want a foreign key to force integrity only if I put data in there. If I do an update at a later time to populate that column, then it should also check the constraint.
(This is likely database server dependant, I'm using MySQL & InnoDB table type)
I believe this is a reasonable expectation, but correct me if I am wrong.
Foreign keys allow key values that are all NULL , even if there are no matching PRIMARY or UNIQUE keys.
Since the Foreign Key constraint requires the referenced key to be unique, the best you can do is allow one row with a key that is NULL. In that case, you will have to replace the Primary Key constraint with a Unique constraint (or index), and allow the column Countries. country_id to be NULL.
Yes, you can enforce the constraint only when the value is not NULL. This can be easily tested with the following example:
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; INSERT INTO child (id, parent_id) VALUES (1, NULL); -- Query OK, 1 row affected (0.01 sec) 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`))
The first insert will pass because we insert a NULL in the parent_id
. The second insert fails because of the foreign key constraint, since we tried to insert a value that does not exist in the parent
table.
I found that when inserting, the null column values had to be specifically declared as NULL, otherwise I would get a constraint violation error (as opposed to an empty string).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With