check foreign key constraint order in postgresql

Question

consider i have a table like lgs_item which has following items

id       title        code
--------------------------------
1         laptop         01

and i have several tables which referenced to lgs_item table.

whenever i want to delete a record from lgs_item it checks foregin key constraint and when there is a problem it says that it is violates a constraint with a constraint name.

but my question is that which constraint checks first?

i had two diffrent data and saw diffrent constraint in checking (that was so weird). is there any special order in checking constraint in postgresql?

Answer 1

Foreign key constraints are implemented by system triggers in PostgreSQL. You can see them with

SELECT c.conname AS constraint_name,
       t.tgname AS trigger_name,
       t.tgfoid::regproc AS trigger_function,
       to_hex(t.tgtype::bigint) AS trigger_type
FROM pg_trigger AS t
   JOIN pg_constraint AS c
      ON t.tgconstraint = c.oid
WHERE t.tgisinternal
  AND t.tgrelid = 'lgs_item'::regclass;

Now triggers of the same type are fired in alphabetical order of the trigger name, so that determines in which order the foreign key constraints are checked. On two different databases with the same definitions, that order could be different, since the trigger names contain the system-generated object ID of the triggers.