Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS parent
(
   id integer PRIMARY KEY 
);
    
CREATE TABLE IF NOT EXISTS child 
(
   id integer PRIMARY KEY, 
   parent_lookup integer references parent(id)
);

ALTER TABLE child
DROP CONSTRAINT IF EXISTS parent_child_constraint,
ADD CONSTRAINT parent_child_constraint
EXCLUDE USING gist(id WITH =, parent_lookup WITH =)
insert into parent (id) values (1);
insert into parent (id) values (2);
insert into child  (id , parent_lookup) values (1, 1);
insert into child  (id , parent_lookup)  values (2, 1);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

insert into child  (id , parent_lookup)  values (2, 1); -- this should not be invalid
ERROR:  duplicate key value violates unique constraint "child_pkey"
DETAIL:  Key (id)=(2) already exists.


insert into child  (id , parent_lookup)  values (1, 2); -- this should be invalid child cannot have a different parent
ERROR:  duplicate key value violates unique constraint "child_pkey"
DETAIL:  Key (id)=(1) already exists.