MySQL set ID of primary child with constraint 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}
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