title: Why does postgreSQL check foreign key constraints twice?
body: <p>I have a postgresql database with two tables, one which has a foreign key constraint which references the other. The constraint is set to cascade on update and delete, so an update on the referenced table should cascade to the referencing table. When I analyzed an update on the referenced table, using explain analyze the I got the following output:</p>
  
  <pre><code>...
  Trigger for constraint foreign_key_constraint on referencedtable: time=33.840 calls=1
  Trigger for constraint foreign_key_constraint on referencingtable: time=2.394 calls=40
  ...
  </code></pre>
  
  <p>I assume the second trigger is called 40 times as this is the amount of times the updated value appeared in this relation.</p>
  
  <p>My question is why does the constraint need to be checked for both tables? Also, why does the trigger for checking the referenced table take so much longer then the referencing table, as it is called only once compared to the referencing table trigger which is called 40 times. If anyone can shed some light as to what exactly is happening or why it takes so long that would be greatly appreciated.</p>
  
  <p>Here is the schema:</p>
  
  <p>CREATE TABLE course
  (
  course_id BIGINT CONSTRAINT course_pk PRIMARY KEY,
  title CHAR(40)
  );</p>
  
  <p>CREATE TABLE lecturer
  (
  lecturer_id BIGINT CONSTRAINT lecturer_pk PRIMARY KEY,
  lec_name CHAR(40)
  );</p>
  
  <p>CREATE TABLE teaches
  (
  lecturer_id BIGINT CONSTRAINT teaches_lecturer_fk1 REFERENCES lecturer (lecturer_id),
  course_id BIGINT CONSTRAINT teaches_course_fk1 REFERENCES course (course_id),
  desc_teaches CHAR(40),
  CONSTRAINT teaches_pk PRIMARY KEY (lecturer_id, course_id)
  );</p>
  
  <p>Query and output:</p>
  
  <p>explain analyze UPDATE lecturer SET lecturer_id = 301 WHERE lecturer_id = 57;</p>
  
  <pre><code>Trigger for constraint teaches_lecturer_fk1 on lecturer: time=33.840 calls=1
  Trigger for constraint teaches_lecturer_fk1 on teaches: time=2.394 calls=40
  </code></pre>
  
  <p>Thanks in advance.</p>