7WbgxL2T

1CREATE TABLE IF NOT EXISTS base
2(
3  __id serial PRIMARY KEY,
4  name varchar(64) NOT NULL
5);
6
7CREATE TABLE IF NOT EXISTS child1
8(
9  __id serial PRIMARY KEY,
10  base int REFERENCES base (__id)      NOT NULL,
11  name varchar(64)                     NOT NULL,
12  UNIQUE (__id, base)
13);
14
15CREATE TABLE IF NOT EXISTS child2
16(
17  __id serial PRIMARY KEY,
18  base int REFERENCES base (__id)      NOT NULL,
19  name varchar(64)                     NOT NULL,
20  UNIQUE (__id, base)
21
22);
23
24CREATE TABLE IF NOT EXISTS secondary_child
25(
26  __id   serial PRIMARY KEY,
27  child1 int,
28  child2 int,
29  base   int         NOT NULL,
30  name   varchar(64) NOT NULL,
31  foreign key (child1, base) references child1 (__id, base),
32  foreign key (child2, base) references child2 (__id, base)
33);
34
35
36INSERT into base (name)
37values ('b1'),
38       ('b2');
39INSERT into child1 (base, name)
40values (1, 'ch1 b1'),
41       (2, 'ch1_b2');
42INSERT into child2 (base, name)
43values (1, 'ch2 b1'),
44       (2, 'ch2_b2');
45
46-- success
47INSERT INTO secondary_child (child1, child2, base, name)
48VALUES (1, 1, 1, 'b1-b1');
49
50-- fail
51INSERT INTO secondary_child (child1, child2, base, name)
52VALUES (1, 2, 1, 'b1-b2');
53
54--fail
55INSERT INTO secondary_child (child1, child2, base, name)
56VALUES (1, 2, 2, 'b1-b2');
57
58--fail
59INSERT INTO secondary_child (child1, child2, base, name)
60VALUES (2, 1, 1, 'b2-b1');
61
62--fail
63INSERT INTO secondary_child (child1, child2, base, name)
64VALUES (2, 1, 2, 'b2-b1');
65
66-- success
67INSERT INTO secondary_child (child1, child2, base, name)
68VALUES (2, 2, 2, 'b2-b2');
69
70select *
71from secondary_child as ch
72       join child1 c1 on ch.child1 = c1.__id
73       join child2 c2 on ch.child2 = c2.__id