Some interdependencies between database tables cannot be (easily) modeled only with foreign keys and control constraints. In my current project, I started writing constraint triggers for all of these conditions, but it looks like I'm going to eventually trigger triggers if I go along this route.
My main questions are:
- Do the triggers and restrictions in the scenario described below actually cover all databases, or is it still possible to add / change data so that the result is inconsistent?
- Did all these triggers really write the right way? I rarely see constraint triggers in third-party database schemas. Do other people just trust the app doesn't mess up?
Minimum Script Example
The central “inventory” table contains all tracked items. Some inventory items are of a special type with special sizes; these additional sizes are stored in separate tables ("books", "figures"). This base table cannot be modified (this is just an example: the actual database obviously has a lot more tables and columns).
Additional requirements:
(A) Each row in the “inventory” table, the type of which is “book”, must have a corresponding row in “books” (the same applies to “images”)
(B) Each row in the “books” table should point to a unique row in the “inventory” of type “book” (the same goes for “images”)
(C) , ""
:
"inventory": id | type | name
----+------+----------------------
a | pic | panda.jpg
b | book | How to do stuff
c | misc | ball of wool
d | book | The life of π
e | pic | Self portrait (1889)
"pictures": inv_id | quality
--------+----------------------------
a | b/w photo?
e | nice, but missing right ear
"books": inv_id | author
--------+--------
b | Hiro P
d | Yann M
:
CREATE TABLE inventory (
id CHAR(1) PRIMARY KEY,
type TEXT NOT NULL CHECK (type IN ('pic', 'book', 'misc')),
name TEXT NOT NULL
);
CREATE TABLE pictures (
inv_id CHAR(1) PRIMARY KEY REFERENCES inventory(id) ON UPDATE CASCADE ON DELETE CASCADE,
quality TEXT
);
CREATE TABLE books (
inv_id CHAR(1) PRIMARY KEY REFERENCES inventory(id) ON UPDATE CASCADE ON DELETE CASCADE,
author TEXT
);
INSERT INTO inventory VALUES
('a', 'pic', 'panda.jpg'),
('b', 'book', 'How to do stuff'),
('c', 'misc', 'ball of wool'),
('d', 'book', 'The life of π'),
('e', 'pic', 'Self portrait (1889)');
INSERT INTO pictures VALUES
('a', 'b/w photo?'),
('e', 'nice, but missing right ear');
INSERT INTO books VALUES
('b', 'Hiro P'),
('d', 'Yann M');
:
CREATE FUNCTION trg_inventory_insert_check_details () RETURNS TRIGGER AS $fun$
DECLARE
type_table_map HSTORE := hstore(ARRAY[
['book', 'books'],
['pic', 'pictures']
]);
details_table TEXT;
num_details INT;
BEGIN
IF type_table_map ? NEW.type THEN
details_table := type_table_map->(NEW.type);
EXECUTE 'SELECT count(*) FROM ' || details_table::REGCLASS || ' WHERE inv_id = $1'
INTO num_details
USING NEW.id;
IF num_details != 1 THEN
RAISE EXCEPTION 'A new "%"-type inventory record also needs a record in "%".',
NEW.type, details_table;
END IF;
END IF;
RETURN NULL;
END;
$fun$ LANGUAGE plpgsql;
CREATE CONSTRAINT TRIGGER insert_may_require_details
AFTER INSERT ON inventory
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE trg_inventory_insert_check_details();
CREATE FUNCTION trg_inv_details_delete () RETURNS TRIGGER AS $fun$
BEGIN
IF EXISTS(SELECT 1 FROM inventory WHERE id = OLD.inv_id) THEN
RAISE EXCEPTION 'Cannot delete "%" record without deleting inventory record (id=%).',
TG_TABLE_NAME, OLD.inv_id;
END IF;
RETURN NULL;
END;
$fun$ LANGUAGE plpgsql;
CREATE CONSTRAINT TRIGGER delete_parent_too
AFTER DELETE ON books
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE trg_inv_details_delete();
CREATE CONSTRAINT TRIGGER delete_parent_too
AFTER DELETE ON pictures
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE trg_inv_details_delete();
CREATE FUNCTION trg_inv_details_check_parent_type () RETURNS TRIGGER AS $fun$
DECLARE
table_type_map HSTORE := hstore(ARRAY[
['books', 'book'],
['pictures', 'pic']
]);
required_type TEXT;
p_type TEXT;
BEGIN
required_type := table_type_map->(TG_TABLE_NAME);
SELECT type INTO p_type FROM inventory WHERE id = NEW.inv_id;
IF p_type != required_type THEN
RAISE EXCEPTION '%.inv_id (%) must point to an inventory item with type="%".',
TG_TABLE_NAME, NEW.inv_id, required_type;
END IF;
RETURN NULL;
END;
$fun$ LANGUAGE plpgsql;
CREATE CONSTRAINT TRIGGER check_parent_inv_type
AFTER INSERT OR UPDATE ON books
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE trg_inv_details_check_parent_type();
CREATE CONSTRAINT TRIGGER check_parent_inv_type
AFTER INSERT OR UPDATE ON pictures
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE trg_inv_details_check_parent_type();
-- TRIGGER: value of inventory.type cannot be changed (provides C)
CREATE FUNCTION trg_fixed_cols () RETURNS TRIGGER AS $fun$
DECLARE
old_rec HSTORE := hstore(OLD);
new_rec HSTORE := hstore(NEW);
col TEXT;
BEGIN
FOREACH col IN ARRAY TG_ARGV LOOP
IF NOT (old_rec ? col) THEN
RAISE EXCEPTION 'Column "%.%" does not exist.', TG_TABLE_NAME, col;
ELSIF (old_rec->col) != (new_rec->col) THEN
RAISE EXCEPTION 'Column "%.%" cannot be modified.', TG_TABLE_NAME, col;
END IF;
END LOOP;
RETURN NULL;
END;
$fun$ LANGUAGE plpgsql;
CREATE CONSTRAINT TRIGGER fixed_cols
AFTER UPDATE ON inventory
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE trg_fixed_cols('type');