How do you constrain a foreign key to a child

Question

A parent entity requires one child entity to be marked. Thus, I've added a field marked_child_id.

How would I constrain this field to only accept ids of child entities of the parent?

Answer 1

You can do this with a circular FK reference. These are tricky because you can't declare a foreign key constraint until after the referenced table is created.

CREATE TABLE parent (
  parent_id INT NOT NULL,
  marked_child_id INT,
  PRIMARY KEY (id)
);

CREATE TABLE child (
  child_id INT NOT NULL,
  parent_id INT NOT NULL,
  PRIMARY KEY (child_id),
  UNIQUE KEY (child_id, parent_id),
  FOREIGN KEY (parent_id) REFERENCES parent(parent_id)
);

The above creates the child -> parent reference, which is expected.

Then you need to add an FK constraint so the marked_child_id references the primary key of the child table:

ALTER TABLE parent
  ADD FOREIGN KEY (marked_child_id, parent_id) 
  REFERENCES child(child_id, parent_id); 

I've made edits to the above to respond to your comments.

• marked_child_id may be NULL, so you can create a parent row before you add the child.
• The foreign key for marked_child_id must reference the combination of the child's two columns child_id and parent_id. That prevents the parent from choosing a child that doesn't reference it in return.

Answer 2

Note: This is an answer to the original question before that was edited.

---

Make the ID and parent unique in child and reference it in marker.

CREATE TABLE parent
             (id integer,
              PRIMARY KEY (id));

CREATE TABLE child
             (id integer,
              parent_id integer,
              PRIMARY KEY (id),
              FOREIGN KEY (parent_id)
                          REFERENCES parent
                                     (id),
              UNIQUE (parent_id,
                      id));

CREATE TABLE marker
             (parent_id integer,
              child_id integer,
              PRIMARY KEY (parent_id),
              FOREIGN KEY (parent_id,
                           child_id)
                          REFERENCES child
                                     (parent_id,
                                      id));