How to enforce by constraint that a referenced row also has a given type

Question

I have multiple tables B,C,D ... referencing one another table A.

Let's just look at A and B:

CREATE TABLE A (
  ID int PRIMARY KEY
, TYPE enum_type
);

CREATE TABLE B (
  A_ID int REFERENCES A(ID)
);

Is it possible to check that A has always one specific TYPE when referenced by table B?

• every row in A referenced by table B has to have TYPE 'X'
• every row in A referenced by table C has to have TYPE 'Y'
• every row in A referenced by table D has to have TYPE 'Z'
• etc.

Is that possible somehow with constraints? Or do I have to use functions? Or what is the best approach for that?

Answer 1

1. FK constraint

If you want to enforce referential integrity with FK constraints, you'll have to include the type in table b redundantly, add a (redundant) UNIQUE constraint on a(id, type) and make that a multicolumn FK constraint on (a_id, type) REFERENCES a(id, type).

Related (with code):

• Enforcing constraints “two tables away”
• Foreign keys referring other foreign keys in PostgreSQL

2. Partitioning

A less strict design could be built with table partitioning. Have a master table a with list partitioning on partition key type. Thus you'd have a partition a_x with all type 'X', and a partition a_y with all type 'Y'. Then create FK constraints to the respective partition and you can be sure of the type.

I suggest list partitioning in Postgres 11 or later. Related (with code example for list partitioning):

• Is there a way to automatically update a view based on daily created tables?

Note that you can't have a FK constraint to the master table. The manual:

foreign keys referencing partitioned tables are not supported.

The FK solution occupies more disk space due to a redundant column and a redundant index. With partitioning OTOH, depending on your version of Postgres and the partitioning method chosen, you have to deal with the limitations / caveats of that particular setup, though.

3. Trigger

You posted another trigger-based solution, which does not occupy extra space nor complicate the relational design. But it's easier to break / circumvent. Not as bullet-proof as the FK solution. Either way, here is an improved version of your idea:

CREATE TABLE a (
    id int PRIMARY KEY,   
    type enum_type NOT NULL  -- ! NOT NULL or adapt the check in the trigger
);

CREATE UNIQUE INDEX a_type_id_idx ON a(type, id);  -- ! see below

CREATE TABLE b (
    a_id int REFERENCES a(id)
);

CREATE FUNCTION type_check()  -- ! rewritten
  RETURNS trigger AS
$type_check$
BEGIN
   IF EXISTS (
      SELECT 
      FROM   a
      WHERE  a.id = NEW.a_id
      AND    a.type = TG_ARGV[0]) THEN
      -- do nothing, all good
   ELSE
      RAISE EXCEPTION 'Type does not match table type!';
   END IF;

   RETURN NEW;
END
$type_check$  LANGUAGE plpgsql;

CREATE TRIGGER tg_check_type_b
BEFORE INSERT OR UPDATE ON b
FOR EACH ROW EXECUTE PROCEDURE type_check('X');

• The index on a(type, id) allows index-only scans for the frequent lookups. Index expressions in this order. Here's why:

  • Is a composite index also good for queries on the first field?

• You checked with IF var_type <> var_allowed_type, but a.type wasn't defined NOT NULL. That would allow mismatches with NULL.

• Assignments are comparatively costly in plpgsql. I simplified the function to make it fast. IF EXISTS ... replacing variables, additional query with assignments should be substantially faster - and also catches a potential mismatch with NULL by inverting the logic.

  • PostgreSQL IF statement

4. Abuse a CHECK constraint

Even faster, and more hacky, yet. Make it NOT VALID to be largely legit. Related cases with code examples and rationale:

• SQL constraint to check whether value doesn't exist in another table
• How to avoid a cyclic dependency (circular reference) between 3 tables?

Answer 2

Erwins Option actually worked for me, but as I have many tables and multiple fields so I went for a different option cause of the overhead in the tables:

CREATE TABLE A (
    ID int PRIMARY KEY,   
    TYPE enum_type
);

CREATE TABLE B (
    A_ID int REFERENCES A(ID)
);

CREATE FUNCTION type_check() RETURNS trigger AS $type_check$
    DECLARE
    var_type enum_type;
    var_allowed_type enum_type;
BEGIN
    SELECT TYPE FROM A INTO var_type WHERE ID = NEW.A_ID;
    var_allowed_type :=  TG_ARGV[0];

    --Check that TYPE is the same as table
    IF var_type <> var_allowed_type THEN
       RAISE EXCEPTION 'Type does not match table type';
    END IF;


    RETURN NEW;
END;  
$type_check$ LANGUAGE plpgsql;

CREATE TRIGGER tg_check_type_B
     BEFORE INSERT OR UPDATE ON B
     FOR EACH ROW EXECUTE PROCEDURE type_check('X');

Answer 3

You can add check constraint for x y condition