title: How to maintain cross-table consistency with triggers in PostgreSQL?
body: <p>Some interdependencies between database tables cannot be (easily) modeled with only foreign keys and check constraints. In my current project, I've begun writing constraint triggers for all these conditions, but it looks like I'm going to end up with hundreds of triggers if I go this route.</p>
  
  <p>My main questions are:</p>
  
  <ol>
  <li>Do the triggers and constraints in the scenario outlined below actually cover all the bases, or is it still possible to add/modify data in such a way that the result would be inconsistent?</li>
  <li>Is writing all those triggers really the right way to go? I rarely see constraint triggers in 3rd-party DB schemas. Do other people just trust the application not to mess up?</li>
  </ol>
  
  <h3>Minimal example scenario</h3>
  
  <p>A central "inventory" table contains all tracked items. Some inventory items are of a specific type with specialized dimensions; these extra dimensions are stored in separate tables ("books", "pictures"). This basic table layout cannot be changed (this is just an example; the actual DB obviously has a lot more tables and columns).</p>
  
  <p>Extra requirements:</p>
  
  <p><strong>(A)</strong> Every row in the "inventory" table whose type is "book" must have a matching row in "books" (same goes for "pictures")</p>
  
  <p><strong>(B)</strong> Every row in the "books" table must point to a unique row in "inventory" whose type is "book" (same goes for "pictures")</p>
  
  <p><strong>(C)</strong> Once inserted, an "inventory" record can never change its type</p>
  
  <h3>Complete database contents:</h3>
  
  <pre class="lang-none prettyprint-override"><code>"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
  </code></pre>
  
  <h3>Create and populate the schema:</h3>
  
  <pre class="lang-sql prettyprint-override"><code>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');
  </code></pre>
  
  <h3>Add triggers to maintain cross-table consistency:</h3>
  
  <pre class="lang-sql prettyprint-override"><code>-- TRIGGER: if inventory.type is 'book', there must be a corresponding record in
  -- "books" (provides A, 1/2)
  
  CREATE FUNCTION trg_inventory_insert_check_details () RETURNS TRIGGER AS $fun$
  DECLARE
      type_table_map HSTORE := hstore(ARRAY[
          ['book', 'books'],
          ['pic',  'pictures']   -- etc...
      ]);
      details_table TEXT;
      num_details INT;
  BEGIN
      IF type_table_map ? NEW.type THEN
          details_table := type_table_map-&gt;(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();
  
  
  -- TRIGGER: when deleting details, parent must be gone, too (provides A, 2/2)
  
  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();
  
  
  -- TRIGGER: details records must point to the correct inventory type (provides B)
  
  CREATE FUNCTION trg_inv_details_check_parent_type () RETURNS TRIGGER AS $fun$
  DECLARE
      table_type_map HSTORE := hstore(ARRAY[
          ['books',    'book'],
          ['pictures', 'pic']   -- etc...
      ]);
      required_type TEXT;
      p_type TEXT;
  BEGIN
      required_type := table_type_map-&gt;(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-&gt;col) != (new_rec-&gt;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');
  </code></pre>