PostgreSQL: enforce 1:1 for optional Plugin

Question

Our product has one core module and several optional plugins.

In the core is a database table called ticket_type.

An optional plugin extends the ticket_type table via a 1:1 relation. This table is called myplugin_ticket_type_extension.

For every row in myplugin_ticket_type_extension there is a row in ticket_type. This gets enforced via a ForeignKey. Up to now to problems :-)

Now the difficult part: How to enforce that there is a row in myplugin_ticket_type_extension for every row in ticket_type?

The difficult part: myplugin is an optional plugin. The core of the product should not know anything about this plugin.

Answer 1

The simplest way to enforce this would be to add a second foreign key to ticket_type which references the extension table.

The difficulty with this circular dependency is that an INSERT into either table will violate a foreign key constraint, before you have a chance to create the other record. You can avoid this by using deferred constraints, which will delay the foreign key check until the transaction commits:

CREATE TABLE ticket_type (id INT PRIMARY KEY);

CREATE TABLE myplugin_ticket_type_extension (
  id INT PRIMARY KEY,
  ticket_type_id INT UNIQUE NOT NULL FOREIGN KEY
    REFERENCES ticket_type (id)
    DEFERRABLE INITIALLY DEFERRED
);
ALTER TABLE ticket_type ADD FOREIGN KEY (id)
  REFERENCES myplugin_ticket_type_extension (ticket_type_id)
  DEFERRABLE INITIALLY DEFERRED;

BEGIN;
INSERT INTO ticket_type VALUES (1);
INSERT INTO myplugin_ticket_type_extension VALUES (1,1);
COMMIT;

An alternative approach which may be worth considering is to use table inheritance:

CREATE TABLE ticket_type (id INT PRIMARY KEY);
CREATE TABLE myplugin_ticket_type_extension (extension_field INT) INHERITS (ticket_type);

INSERT INTO myplugin_ticket_type_extension (id, extension_field) VALUES (1,1);

Records inserted into the extension table will be shown when you query ticket_type, so your core module should be unaffected. You can prevent inserts directly into the ticket_type table by adding a trigger, which could either block inserts altogether (by raising an exception), or could automatically redirect new records to the extension table:

CREATE FUNCTION ticket_type_trg() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO myplugin_ticket_type_extension (id) VALUES (new.id);
  RETURN NULL;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER ticket_type_trg
  BEFORE INSERT ON ticket_type FOR EACH ROW
  EXECUTE PROCEDURE ticket_type_trg();

Answer 2

+1 on the deferrable constraint. There is one thing missing from that answer and that is that you can run SET CONSTRAINTS IMMEDIATE to force PostgreSQL to run the checks then instead of waiting for commit. That is usually better for debugging etc if your application knows that the constraints are there.

In that case, your queries look like:

BEGIN;
INSERT INTO ticket_type VALUES (1);
INSERT INTO myplugin_ticket_type_extension VALUES (1,1);
SET CONSTRAINTS ALL IMMEDIATE;
-- Do other work here, knowing that the above foreign key is 
-- already enforced
COMMIT;

Note that the above example gives you a problem if you have multiple deferred constraints. In those cases you want to do that by name (SET CONSTRAINTS name1, name2 IMMEDIATE)