Can a MySQL FOREIGN KEY CONSTRAINT reference a column value rather than a column name?

Question

I have a child table whose rows are related to various parent tables by parent_table_name, and parent_id columns.

Can I set up a foreign key constraint for these relationships?

In the tables below, I can run queries such as SELECT * FROM parent_1 p1 JOIN child c ON p1.id = c.parent_id AND c.parent_table_name = 'parent_1'; to get a row from parent_1 and any related rows from child.

CREATE TABLE parent_1 (
 id INT NOT NULL AUTO_INCREMENT
 , PRIMARY KEY(id)
);
INSERT INTO parent_1 VALUES(1);
CREATE TABLE parent_2 (
 id INT NOT NULL AUTO_INCREMENT
 , PRIMARY KEY(id)
);
INSERT INTO parent_2 VALUES(1);
CREATE TABLE child (
 id INT NOT NULL AUTO_INCREMENT
 , parent_table_name VARCHAR(16) NOT NULL
 , parent_id INT NOT NULL
 , some_interesting_value VARCHAR(255) NOT NULL
 , PRIMARY KEY(id)
 , KEY (parent_table_name, parent_id)
);
INSERT INTO child VALUES(NULL, 'parent_1', 1, 'Hello world');
INSERT INTO child VALUES(NULL, 'parent_2', 1, 'Goodbye world');

I have tried adding a constraint as below, but got a 1064 error.

ALTER TABLE parent_1 ADD CONSTRAINT p1_children FOREIGN KEY ("parent_1", id)
 REFERENCES child (parent_table_name, parent_id);
ERROR 1064 (42000): You have an error in your SQL syntax;
 check the manual that corresponds to your MySQL server version
 for the right syntax to use near '"parent_1", id) REFERENCES
 child (parent_table_name, parent_id)' at line 1

Is there some way to set up this sort of constraint? Or is the only way to have a third table that has the primary keys from the parent and child table?

• matigo: It looks like your reference is backwards. The child record refers to a parent’s key, not the other way around 🤔
• Lennart - Slava Ukraini: Create child_1 and child_2 tables and let child be a view over these tables

Answer 1

No. A foreign key cannot use a literal value, nor can it use an expression or a virtual generated column. It must use a column identifier.

FOREIGN KEY <name> (<column1>, <column2>, ...)
REFERENCES <tablename> (<column1>, <column2>, ...)

It looks like you're trying to do polymorphic-associations. The easiest solution to do what you're doing and have a foreign key constraint enforce it is to use two different columns, one of which is NULL and the other references one of the parent tables.

CREATE TABLE child (
 id INT NOT NULL AUTO_INCREMENT
 , parent1_id INT NULL
 , parent2_id INT NULL
 , some_interesting_value VARCHAR(255) NOT NULL
 , PRIMARY KEY(id)
 , CHECK ((parent1_id IS NULL + parent2_id IS NULL) = 1) 
 , FOREIGN KEY (parent1_id) REFERENCES parent_1 (id)
 , FOREIGN KEY (parent2_id) REFERENCES parent_2 (id)
);
INSERT INTO child VALUES(NULL, 'parent_1', NULL, 1, 'Hello world');
INSERT INTO child VALUES(NULL, NULL, 'parent_2', 1, 'Goodbye world');