Back

Explore Courses Blog Tutorials Interview Questions
0 votes
3 views
in SQL by (20.3k points)

I have a table that 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-dependent, I'm using MySQL & InnoDB table type)

I believe this is a reasonable expectation, but correct me if I am wrong.

1 Answer

0 votes
by (40.7k points)

If the value is not NULL then you can enforce the constraint. 

You can easily test this 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`))

Here, you can observe that the first insert will pass because we insert a NULL in the parent_id and the second insert fails because of the foreign key constraint. As we are trying to insert a value that does not exist in the parent table.

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Oct 5, 2019 in SQL by Tech4ever (20.3k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

30.5k questions

32.6k answers

500 comments

108k users

Browse Categories

...