sqlserver2019.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];