Catching a FK constraint violation error when trying to delete a record from a table that is a child in the hierarchy

Question

I have a simple configuration,

CREATE TABLE dev.user (
    id serial PRIMARY KEY,
    name VARCHAR (1024) NOT NULL
);

CREATE TABLE dev.schema (
    id serial PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES dev.user (id)
);

CREATE TABLE dev.schema_table (
    id SERIAL PRIMARY KEY,
    schema_id BIGINT NOT NULL REFERENCES dev.schema (id)
);

Basically, a user can own many schema records, and a schema record can have many schema_table records linked to it.

I am trying to delete a record from dev.schema_table and I'm catching an error,

update or delete on table "schema_table" violates
    foreign key constraint "schema_table_schema_id_fkey" on table "schema_table"

I'm a little confused, as to why I'm running into this error.

I first create 3 schema_table records with the same value for the field schema_id and then I try to delete one of these schema_table records.

What am I doing wrong and why am I getting this FK constraint violation error?

Answer 1

This is not an answer, but more a lengthy remark.

I tried to reproduce the issue:

tkdb=> create schema dev;
CREATE SCHEMA
tkdb=> CREATE TABLE dev.user (
tkdb(>     id serial PRIMARY KEY,
tkdb(>     name VARCHAR (1024) NOT NULL
tkdb(> );
CREATE TABLE
tkdb=> 
tkdb=> CREATE TABLE dev.schema (
tkdb(>     id serial PRIMARY KEY,
tkdb(>     user_id BIGINT NOT NULL REFERENCES dev.user (id)
tkdb(> );
CREATE TABLE
tkdb=> 
tkdb=> CREATE TABLE dev.schema_table (
tkdb(>     id SERIAL PRIMARY KEY,
tkdb(>     schema_id BIGINT NOT NULL REFERENCES dev.schema (id)
tkdb(> );
CREATE TABLE
tkdb=> insert into dev.user values (1, 'Hans');
INSERT 0 1
tkdb=> insert into dev.schema values (3, 1);
INSERT 0 1
tkdb=> insert into dev.schema_table values (10, 3);
INSERT 0 1
tkdb=> insert into dev.schema_table values (11, 3);
INSERT 0 1
tkdb=> insert into dev.schema_table values (12, 3);
INSERT 0 1
tkdb=> delete from dev.schema_table where id = 11;
DELETE 1

And as you can see from the output, it works!

What do you do differently?