0

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?

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.