What is the difference between on delete cascade & on update cascade mysql?
I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE and ON DELETE CASCADE
My Parent Table
CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
My Question is: What is the difference between the following sql queries.
ON DELETE CASCADE
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
ON UPDATE CASCADE
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON UPDATE CASCADE
) ENGINE=INNODB;
ON UPDATE CASCADE ON DELETE CASCADE
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=INNODB;
Are there any errors in the queries? What do these queries (1,2 & 3) mean?? Are they the same???
On Delete Cascade vs on update cascade mysql These two are actions to be performed, respectively, when the referenced record on the parent table changes its id and when it gets deleted.
If you execute:
UPDATE parent SET id = -1 WHERE id = 1;And there is at least one record on a child with parent_id = 1, 1) will fail; in cases 2) and 3), all records with parent_id = 1 are updated to parent_id = -1.
If you execute:
DELETE FROM parent WHERE id = 1;And there is at least one record on a child with parent_id = 1, 2) will fail; in cases 1) and 3), all records with parent_id = 1 are deleted.
3) is syntactically correct.