logically_deleted_with_fks.sql

-- Tables which are logically "deleted" with a `deleted` column,
-- but still respect foreign key constraints.. of a sort.

CREATE SCHEMA test;

CREATE TABLE test.foo_full (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    deleted BOOL DEFAULT false,
    UNIQUE (id, deleted),
    CHECK (deleted IS DISTINCT FROM true)
);

CREATE VIEW test.foo AS
SELECT id, name
FROM test.foo_full
WHERE NOT deleted;

CREATE FUNCTION test.delete_foo() RETURNS trigger AS $$
    BEGIN
        UPDATE test.foo_full SET deleted = null WHERE id = old.id;
        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_foo INSTEAD OF DELETE ON test.foo
    FOR EACH ROW EXECUTE FUNCTION test.delete_foo();

CREATE TABLE test.bar_full (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    foo_id BIGINT NOT NULL,
    deleted BOOL DEFAULT false,
    UNIQUE (id, deleted),
    FOREIGN KEY (foo_id, deleted) REFERENCES test.foo_full(id, deleted),
    CHECK (deleted IS DISTINCT FROM true)
);

CREATE VIEW test.bar AS
SELECT id, name, foo_id
FROM test.bar_full
WHERE NOT deleted;

CREATE FUNCTION test.delete_bar() RETURNS trigger AS $$
    BEGIN
        UPDATE test.bar_full SET deleted = null WHERE id = old.id;
        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_bar INSTEAD OF DELETE ON test.bar
    FOR EACH ROW EXECUTE FUNCTION test.delete_bar();

CREATE TABLE test.baz_full (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    bar_id BIGINT NOT NULL,
    deleted BOOL DEFAULT false,
    UNIQUE (id, deleted),
    FOREIGN KEY (bar_id, deleted) REFERENCES test.bar_full(id, deleted),
    CHECK (deleted IS DISTINCT FROM true)
);

CREATE VIEW test.baz AS
SELECT id, name, bar_id
FROM test.baz_full
WHERE NOT deleted;

CREATE FUNCTION test.delete_baz() RETURNS trigger AS $$
    BEGIN
        UPDATE test.baz_full SET deleted = null WHERE id = old.id;
        RETURN OLD;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER delete_baz INSTEAD OF DELETE ON test.baz
    FOR EACH ROW EXECUTE FUNCTION test.delete_baz();

-- foo_a
-- ├ bar_one
-- └ bar_two
-- foo_b
-- └ bar_three
--   └ baz_α
-- foo_c
-- └ bar_four
-- foo_d
INSERT INTO test.foo (name) VALUES ('foo_a'), ('foo_b'), ('foo_c'), ('foo_d');
INSERT INTO test.bar (name, foo_id)
    VALUES ('bar_one', 1), ('bar_two', 1), ('bar_three', 2), ('bar_four', 3);
INSERT INTO test.baz (name, bar_id) VALUES ('baz_α', 3);

DELETE FROM test.foo WHERE name = 'foo_d';
DELETE FROM test.foo WHERE name = 'foo_c';  -- /!\ this fails
DELETE FROM test.bar WHERE name = 'bar_four';
DELETE FROM test.foo WHERE name = 'foo_c';

DELETE FROM test.foo WHERE name = 'foo_b';  -- /!\ this fails
DELETE FROM test.bar WHERE name = 'bar_three';  -- /!\ this fails
DELETE FROM test.baz WHERE name = 'baz_α';
DELETE FROM test.foo WHERE name = 'foo_b';  -- /!\ this fails
DELETE FROM test.bar WHERE name = 'bar_three';
DELETE FROM test.foo WHERE name = 'foo_b';