970_90

Tuesday, 22 January 2013

MySQL Cascade Example

MySQL Cascade Example

Q1: When parent record deleted, child must be deleted automatically. But if child record deleted, parent should not effected.

Q2: I want to understand the mysql constraint cascade.

Q3. How a child record deleted automatically when parent deleted.

Above all Questions have only one answer that is below with fine example.

Create a table products
CREATE TABLE products (
    id int unsigned not null primary key AUTO_INCREMENT,
    name varchar(255) default null 
); 

Create a table reviews, as each product have one or more reviews

CREATE TABLE reviews (
    id int unsigned not null primary key AUTO_INCREMENT,
    review_by int(11) unsigned not null,
    product_id int(11) unsigned not null,
    points int(11),
    FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE  ON UPDATE CASCADE
  )


Now, if you delete a product, then its review (product's review) will be automatically deleted. but if you delete the child parent record will be untouched/uneffected.

No comments:

Post a Comment