SQL Create Trigger Before Delete vs. Foreign Key On Delete

Question

I have one table referencing another. As I see - there are two ways to delete cascading:

What is the difference between CREATE TRIGGER BEFORE DELETE and FOREIGN KEY ON DELETE? Are there any differences in performance?

I came up with this advantage of FOREIGN KEY:
The cascading delete is more obvious because it's attached in the table definition.

Full question:
I have the two tables:
project(id, ...) <- works_on(id, project_id, ...)

What are the differences in

CREATE TABLE works_on (
  ...
  FOREIGN KEY (project_id) REFERENCES project ON DELETE CASCADE
  ...
);

and

CREATE TRIGGER trigger_delete_cascading
  BEFORE DELETE ON project
  DELETE works_on
    WHERE project_id = id;

They are very similar. I would advise you to always go with the cascading delete (if that is what you want), so the database takes care of relational integrity. It also has the advantage of validating the value in the column. — Gordon Linoff, Feb 08 '17 at 13:53
In my case your comment and Serge's accepted answer solve this mystery. Sagar Gangwal also advised to use "Foreign Key" concerning more consistency: http://stackoverflow.com/a/42114888/1700776 — SUhrmann, Feb 08 '17 at 14:16

score 4 · Accepted Answer · answered Feb 08 '17 at 13:49

4

A FOREIGN KEY will restrict values that can be stored in the project_id column of the works_on table. You will not be able to set a value that does not exist in the project table.

A TRIGGER does not restrict the range of values that can be stored.

answered Feb 08 '17 at 13:49

Serge

3,986
2
17
37

score 3 · Answer 2 · answered Feb 08 '17 at 13:47

If wrote trigger BEFORE delete,will DELETE record from CHILD TABLE and due to some Server error or Other constraint if record is unable to delete from MAIN TABLE(PARENT) then it makes redundant data. So whenever you required delete plus more action like maintaining LOG table then only you have to go with Trigger.Otherwise ON DELETE CASCADE is great to work. Hope this will helps you.

SQL Create Trigger Before Delete vs. Foreign Key On Delete

2 Answers2