pg14.sql

-- two schemas
create schema "janEatsHisHat";
create schema "juliusQuitsHisJob";

-- this one in the first schema, it's the "parent" table
create table "janEatsHisHat"."delicious" (id int primary key);

-- this one in the second, the "child" table
create table "juliusQuitsHisJob"."arbeitsamt" (
    id int primary key,
    hat int,
    constraint amt_hat_fk foreign key (hat) references "janEatsHisHat"."delicious"(id) on delete cascade
);

-- three parents
insert into "janEatsHisHat"."delicious" (id) values (1), (2), (3);

-- two children referencing one of the parents each
insert into "juliusQuitsHisJob"."arbeitsamt" (id, hat) values (1, 1), (2, 2), (3, 3);

-- lists three parents
select * from "janEatsHisHat"."delicious";

-- lists three children
select * from "juliusQuitsHisJob"."arbeitsamt";

-- a parent gets deleted
delete from "janEatsHisHat"."delicious" where id = 1;

-- lists two children
select * from "juliusQuitsHisJob"."arbeitsamt";