Question 262805 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres 11 not enforcing foreign key constraint?

Our PostgreSQL 11 database has tables A and B.

postgres=# d A;
                              Table "public.A"
       Column        |            Type             | Collation | Nullable | Default 
---------------------+-----------------------------+-----------+----------+---------
 id                  | bigint                      |           | not null | 
 b_id                | bigint                      |           | not null | 
 
Indexes:
    "a_pkey" PRIMARY KEY, btree (id)
    "idx_a_bid" btree (b_id)
Foreign-key constraints:
    "fk_a_bid" FOREIGN KEY (b_id) REFERENCES A(id)

postgres=# select count(*) from A where b_id = 522039;
 count 
-------
    90
(1 row)
                  
postgres=# select count(*) from B where id = 522039;
 count 
-------
     0
(1 row)

There seems to be an unenforced FK from A.b_id to B.id.

Triggers were never disabled on these tables (so in theory FK checks have been applied).

What is an explanation for this other than someone disabling triggers in the past and removing rows from B?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This is likely caused by some code setting SET session_replication_role = replica before data manipulation. This statement disables triggers, including foreign key constraint checks.


  2. 0

    What could make PostgreSQL violate a foreign key constraint? There are three possible answers, and each has to do with the fact that foreign keys are implemented by system triggers on the referenced and the referencing tables. These triggers are not shown in the output of dt or in information_schema.triggers, and you have to query the catalog pg_trigger to see them.

    Violate a foreign key by setting session_replication_role

    If session_replication_role = replica, normal triggers don’t fire. Only triggers that have been changed with on of these statements will fire:

    ALTER TABLE ... ENABLE REPLICA TRIGGER ...
ALTER TABLE ... ENABLE ALWAYS TRIGGER ...

    It takes a superuser to change session_replication_role, and it takes a superuser to modify the system triggers that implement foreign keys.

    Violate a foreign key constraint by disabling triggers on a table

    This can be done with

    ALTER TABLE ... DISABLE TRIGGER ALL

    on the referencing or referenced table. This statement can only be executed by a superuser. Depending on whether you disabled triggers on the referenced or the referencing table, certain aspects of foreign key constraints will no longer be checked.

    Violate a cascading foreign key with a user-defined trigger

    Create a constraint with

    ALTER TABLE a ADD FOREIGN KEY (b_id) REFERENCES b (id) ON DELETE CASCADE;

    Then create a trigger like this:

    CREATE FUNCTION cancel_delete() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   RETURN NULL;
END;$$;

CREATE TRIGGER cancel_delete BEFORE DELETE ON a
   FOR EACH ROW EXECUTE FUNCTION cancel_delete();

    Now if you delete a row in b, a system trigger will delete rows in a, but the trigger you created will abort these deletes without an error, since it returns NULL. That will break referential integrity.

    Exotic ways to create broken foreign keys

    • you can create a NOT VALID foreign key constraint (but that would show up in d)

    • you can experience data corruption that silently loses data in one of the tables

    • you can directly mess with the catalogs, for example by inserting a row in pg_constraint

    So what caused the foreign key to be broken?

    The first two methods require a superuser to do something wrong, but the third method is open to normal users. If you can exclude that a superuser has messed with the database, it has to be the third method.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search