How can I define a one to many constraint between columns of the same table? I'm creating an IDs table in PostgreSQL to represent the relationship between multiple foreign IDs - call them a, b and c.
I happen to know that the foreign IDs have one-to-many relationships between them:
a 1..* b
b 1..* c
I'd like to represent them as a single table (see below), but enforce the one-to-many invariant between a &amp; b, &amp; between b and c.
Is that possible?
Examples:
The following insertions would be illegal:




pk
a
b
c
reason




p15
a1
b11
c111
c111 already exists


p16
a1
b21
c213
b21 already exists for a2


p17
a2
b11
c214
b11 already exists for a1




Given the following legal table:




pk
a
b
c




p1
a1
b11
c111


p2
a1
b11
c112


p3
a1
b12
c121


p4
a1
b12
c122


p5
a2
b21
c211


p6
a2
b21
c212


p7
a2
b22
c221


p8
a2
b22
c222


p9
null
b31
c311


p10
a3
null
c312


p11
a3
b31
null


p12
null
null
c314


p13
null
b31
null


p14
a3
null
null
This has to be done by TRIGGER, because it is too complex for CHEKCconstraint
For updating you need a similar trigger/function


CREATE TABLE tabl1
    (&quot;pk&quot; varchar(5), &quot;a&quot; varchar(6), &quot;b&quot; varchar(6), &quot;c&quot; varchar(6))
;
    
INSERT INTO tabl1
    (&quot;pk&quot;, &quot;a&quot;, &quot;b&quot;, &quot;c&quot;)
VALUES
    ('p1', 'a1', 'b11', 'c111'),
    ('p2', 'a1', 'b11', 'c112'),
    ('p3', 'a1', 'b12', 'c121'),
    ('p4', 'a1', 'b12', 'c122'),
    ('p5', 'a2', 'b21', 'c211'),
    ('p6', 'a2', 'b21', 'c212'),
    ('p7', 'a2', 'b22', 'c221'),
    ('p8', 'a2', 'b22', 'c222'),
    ('p9', NULL, 'b31', 'c311'),
    ('p10', 'a3', NULL, 'c312'),
    ('p11', 'a3', 'b31', NULL),
    ('p12', NULL, NULL, 'c314'),
    ('p13', NULL, 'b31', NULL),
    ('p14', 'a3', NULL, NULL)
;




CREATE OR REPLACE FUNCTION check_insert()
RETURNS TRIGGER AS $$
   BEGIN
   IF EXISTS(SELECT 1 FROM tabl1 WHERE &quot;c&quot; = NEW.&quot;c&quot; AND &quot;b&quot; = NEW.&quot;b&quot; ) then
       raise EXCEPTION 'The column %  already  with %',NEW.&quot;c&quot;,NEW.&quot;b&quot;;
   ELSIF EXISTS(SELECT 1 FROM tabl1 WHERE &quot;a&quot; &lt;&gt; NEW.&quot;a&quot; AND &quot;b&quot; = NEW.&quot;b&quot; ) then
           raise EXCEPTION 'The column %  already  has already a column',NEW.&quot;b&quot;;
   END IF;
   RETURN NEW;
   END;
$$ LANGUAGE plpgsql;




CREATE  TRIGGER trigger_name BEFORE
 INSERT
ON tabl1
FOR EACH ROW
EXECUTE PROCEDURE check_insert();




INSERT INTO tabl1
    (&quot;pk&quot;, &quot;a&quot;, &quot;b&quot;, &quot;c&quot;)
VALUES
    ('p15', 'a1', 'b11', 'c111')


ERROR:  The column c111  already  with b11

CONTEXT:  PL/pgSQL function check_insert() line 4 at RAISE





INSERT INTO tabl1
    (&quot;pk&quot;, &quot;a&quot;, &quot;b&quot;, &quot;c&quot;)
VALUES
('p16' , 'a1'   , 'b21'  , 'c213' )


ERROR:  The column b21  already  has already a column

CONTEXT:  PL/pgSQL function check_insert() line 6 at RAISE





INSERT INTO tabl1
    (&quot;pk&quot;, &quot;a&quot;, &quot;b&quot;, &quot;c&quot;)
VALUES
('p17',   'a2',   'b11'   ,'c214')


ERROR:  The column b11  already  has already a column

CONTEXT:  PL/pgSQL function check_insert() line 6 at RAISE





SELECT * FROM tabl1


pk  | a    | b    | c   
:-- | :--- | :--- | :---
p1  | a1   | b11  | c111
p2  | a1   | b11  | c112
p3  | a1   | b12  | c121
p4  | a1   | b12  | c122
p5  | a2   | b21  | c211
p6  | a2   | b21  | c212
p7  | a2   | b22  | c221
p8  | a2   | b22  | c222
p9  | null | b31  | c311
p10 | a3   | null | c312
p11 | a3   | b31  | null
p12 | null | null | c314
p13 | null | b31  | null
p14 | a3   | null | null


db&lt;&gt;fiddle here