CODEWITHSUNDEEP
mysqlsqlforeign-keyssql-deletecascading-deletes
woid_pointer
woid_pointer

Reputation: 642

SQL Create Trigger Before Delete vs. Foreign Key On Delete

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;

Upvotes: 3

Views: 1895

Answers (2)

Serge
Serge

Reputation: 4036

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.

Upvotes: 4

Sagar Gangwal
Sagar Gangwal

Reputation: 7980

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.

Upvotes: 3

Related Questions