CODEWITHSUNDEEP
postgresqlconstraints
Huluk
Huluk

Reputation: 874

Enforce type equality of foreign keys in Postgres

I have an entity table with multiple entity types and a table of entity relations which needs to enforce that it only contains relations between entities of the same type.

Currently, I have two approaches to this:

  1. CREATE TABLE entity (
  id uuid PRIMARY KEY,
  type my_enum_type NOT NULL,
  -- … more 
);

CREATE TABLE relation (
  id uuid PRIMARY KEY,
  x uuid REFERENCES entity NOT NULL,
  y uuid REFERENCES entity NOT NULL,
  CHECK(entity(x).type = entity(y).type)
  -- Doesn't work because CHECK cannot reference other tables.
);

  2. Fortunately I currently have only two types, and don't expect that to change soon. But inheritance does not mix well with foreign keys, so it gets quite verbose:

    CREATE TABLE entity (
  id uuid PRIMARY KEY,
  -- … more fields
);
CREATE TABLE entity_a (
  PRIMARY KEY (id)
) INHERITS (entity);
CREATE TABLE entity_b (
  PRIMARY KEY (id)
) INHERITS (entity);

CREATE TABLE relation (
  id uuid PRIMARY KEY,
  x uuid NOT NULL,
  y uuid NOT NULL,
  -- … more fields
);
CREATE TABLE relation_a (
  PRIMARY KEY (id),
  FOREIGN KEY (x) REFERENCES entity_a (id),
  FOREIGN KEY (y) REFERENCES entity_a (id)
) INHERITS (relation);
CREATE TABLE relation_b (
  PRIMARY KEY (id),
  FOREIGN KEY (x) REFERENCES entity_b (id),
  FOREIGN KEY (y) REFERENCES entity_b (id)
) INHERITS (relation);

The second approach definitely has the advantage that it works, but it's verbose, not extensible, and the only advantage over completely separate table definitions is that it avoids copying all additional fields (and then maybe forgetting to update them in both places).

Any suggestions on how to solve this more elegantly?

Upvotes: 0

Views: 131

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246808

The following statements will add constraints to your solution 1) so that the condition is always satisfied:

/* we need a (redundant) UNIQUE constraint as target for foreign keys */
ALTER TABLE entity ADD UNIQUE (type, id);

/* add a (redundant) "type" column and fill it from "entity" */
ALTER TABLE relation ADD type my_enum_type;

UPDATE relation SET type = e.type
   FROM entity AS e
   WHERE relation.x = e.id;

ALTER TABLE relation ALTER type SET NOT NULL;

/* now we can add foreign keys that guarantee your condition */
ALTER TABLE relation ADD FOREIGN KEY (type, x) REFERENCES entity (type, id);
ALTER TABLE relation ADD FOREIGN KEY (type, y) REFERENCES entity (type, id);

/* remove the bloat (optional) */
VACUUM (FULL) relation;

True, it adds a redundant column and an redundant constraint, but I think it is the most elegant and natural way to ensure your condition.

Upvotes: 1

Related Questions