CODEWITHSUNDEEP
mysqldatabasephpmyadminforeign-keysforeign-key-relationship
user3962692
user3962692

Reputation:

Difference between On Delete Cascade & On Update Cascade in 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 RESTRICT

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.

1)

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;

2)

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;

And are there any errors in the following query??

3)

 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;

What does these queries (1,2 & 3) mean?? Are they same???

Gracias.

Upvotes: 2

Views: 12511

Answers (1)

Sampat Badhe
Sampat Badhe

Reputation: 9085

on delete cascade

It will delete all the child records when parent record is deleted, so that there will be no child record when parent is deleted.

on update cascade

It will update the child records parent_id when parent record id that change. In rare case we use on update cascade

eg:- suppose your parent id is 4 digit and due to expansion later on you need change it to 10 digit. In that case, ON UPDATE CASCADE would allow you to change the primary key value and any tables that have foreign key references to the value will be changed accordingly.

Upvotes: 13

Related Questions