Tuesday, 28 April 2015

Difference between On Delete Cascade & On Update Cascade in mysql

My Parent Table
CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;
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;

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 do these queries (1,2 & 3) mean?? Are they same???

1) means that if the parent is deleted, the child is also deleted 

2) means that if the parent primary key is changed, the child value will also change to reflect that - again in my opinion, not a great idea. If you're changing PRIMARY KEYs with any regularity (or even at all), there's something wrong with your design.

3) Means that if you update or delete the parent, the change is cascaded to the child.

No comments:

Post a Comment