Wednesday 23 January 2013

MySQL Constraint Example

Q1: Table restrict for delete, if child table have records.

Q2: Mysql constraint restrict.

Q3. How to prevent from delete a parent record, if child exists.

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

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

Create a table reviews, as each products 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,

Now, if you try to delete a product having one or more review(s), you will get an mysql error similar to following.

SQL query:
DELETE FROM `products` WHERE `products`.`id` =1
MySQL said:

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`abc`.`reviews`, CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`))

You can only delete the product, after deleted its reviews.

You can also add constraints after table creation, Use following query as example.
alter table `reviews` add constraint `FK_reviews_check` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE RESTRICT ON DELETE RESTRICT;
MySQL constraints Requirement
1. Both FOREIGN KEY table and REFERENCE table must have same Storage Engine.
2. Both FOREIGN KEY and Reference Id must have same datatype.
3. Both FOREIGN KEY and Reference Id must have same attributes means either both un-signed OR signed.