Search code examples
sqlmysql

One-level one-to-many relationship

I have a table (representing applications, but this is not really relevant for this question), and I would like to have a one-to-many parent-child relationship.

ID Name ParentID
1 Parent null
2 Child A 1
3 Child B 1

I would also like to enforce the relationship to be one-level only, i.e. if an entry has a parent it should not be used as parent by other entries:

ID Name ParentID Ok
1 Parent null Yes
2 Child 1 No : has both parent and children
3 Grandchild 2 No : child of a child

I was thinking of enforcing this through code, but I was wondering if there is a native way to enforce this, e.g. with some constraint.

I'm using a MySQL database, in case that's somehow relevant.

Solution

  • See simple example

    create table test( ID int, Name varchar(20), ParentID int
  ,primary key (id)
  ,xkey int default (coalesce(ParentID,Id))
  ,unique key (Id,xkey)
  ,FOREIGN KEY (ParentId) REFERENCES test(id)
  ,FOREIGN KEY (ParentId,xKey) REFERENCES test(Id,xkey)
  );
-- Insert root
insert into test(id,Name,ParentID) values
 (1,'Parent',   null)
;

select * from test;
    ID Name ParentID xkey
    1 Parent null 1

    Insert child

    insert into test(id,Name,ParentID) values
 (2,'Child A-2',    1)

;
select * from test;
    ID Name ParentID xkey
    1 Parent null 1
    2 Child A-2 1 1

    Main test - insert child of child

    insert into test(id,Name,ParentID) values
 (3,'Child A-3',    2)
;
select * from test;

    Cannot add or update a child row: a foreign key constraint fails (`fiddle`.`test`, CONSTRAINT `test_ibfk_2` FOREIGN KEY (`ParentID`, `xkey`) REFERENCES `test` (`ID`, `xkey`))

    We can't insert this row

    ID Name ParentID xkey
    3 Child A-3 2 2

    Insert next root

    insert into test (id,Name,ParentID) values
 (4,'Child C',  null)
;
select * from test;
    ID Name ParentID xkey
    1 Parent null 1
    2 Child A-2 1 1
    4 Child C null 4

    Insert childs

    insert into test (id,Name,ParentID) values
 (5,'Child C-5',    4)
,(6,'Child C-6',    4)
,(7,'Child C-7',    4)
;
select * from test;

    Records: 3  Duplicates: 0  Warnings: 0
    ID Name ParentID xkey
    1 Parent null 1
    2 Child A-2 1 1
    4 Child C null 4
    5 Child C-5 4 4
    6 Child C-6 4 4
    7 Child C-7 4 4

    fiddle