0

This question already has an answer here:

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}

marked as duplicate by MDCCL, Erik Darling, Community Feb 9 '18 at 0:32

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • It is possible but I wouldn't recommend that. – Paul Spiegel Feb 9 '18 at 1:11
  • Playing favorites gets you into trouble. – Rick James Feb 16 '18 at 18:23
0

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.