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?

It looks like your reference is backwards. The child record refers to a parent’s key, not the other way around 🤔 — matigo
– matigo, Commented Jul 23, 2021 at 4:04
Create child_1 and child_2 tables and let child be a view over these tables — Lennart - Slava Ukraini
– Lennart - Slava Ukraini, Commented Jul 23, 2021 at 15:17

Bill Karwin · Accepted Answer · 2021-07-25 17:52:21Z

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');

Stack Exchange Network

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

1 Answer 1

Your Answer

Linked

Hot Network Questions

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

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions