I have two MySQL tables where the primary relationship between the two is one-to-many. I also need a one-to-one relationship in the parent_table with the ID for the primary record from the child_table. You can kind of think of it as like a dad having lots of kids but having one kid that's his favorite ;P. Is it possible to setup a DB constraint such that a parent can have the ID of a child only if that child is a child of the parent?

parent_table (One)
  id: Primary Key
  primary_child_id: {ID from child table}

child_table (Many)
  id: Primary Key
  parent_table: {ID from parent table}
  • It is possible but I wouldn't recommend that. Commented Feb 9, 2018 at 1:11
  • Playing favorites gets you into trouble.
    – Rick James
    Commented Feb 16, 2018 at 18:23

1 Answer 1


Circular foreign key

create table parent
    id int primary key,
    primary_child_id int
create table childs
    id int primary key,
    parent_id int references parent (id)
alter table parent 
    add constraint fkPrimaryChild
    foreign key (primary_child_id)
    references childs (id)

First time you insert a new parent record, primary_child_id must be null.

insert into parent values (1, null);
insert into childs values (1, 1), (2, 1), (3, 1);

After you have inserted one or more child records, then you can assign primary_child_id.

update parent set primary_child_id = 1 where id = 1;
delete from childs where id = 1;
Cannot delete or update a parent row: a foreign key constraint fails
(`fiddle_JKZMCYWHMDENXXNQQOMG`.`parent`, CONSTRAINT `fkPrimaryChild` 
FOREIGN KEY (`primary_child_id`) REFERENCES `childs` (`id`))

dbfiddle here

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