Home arrow PHP arrow Page 2 - Cascading Deletes of Database Rows in MySQL with Foreign Key Constraints

Review: using foreign key constraints when updating databases - PHP

Foreign key constraints are a powerful feature provided by many modern RDBMS that allow you to automatically trigger specified actions, such as deletions or updates of the records of database tables that maintain a predefined relationship with each other. This is the second part of a six-part article series that shows you how to work with foreign key constraints in MySQL.

TABLE OF CONTENTS:
  1. Cascading Deletes of Database Rows in MySQL with Foreign Key Constraints
  2. Review: using foreign key constraints when updating databases
  3. Deleting database records without foreign key constraints
  4. Deleting database rows with foreign key constraints
By: Alejandro Gervasio
Rating: starstarstarstarstar / 1
December 10, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

As I mentioned, the previous article of this series discussed how to utilize foreign key constraints to maintain the relationship between two sample InnoDB tables. The first one stored some simple blog entries, and the second one housed the comments made to those entries.

The neat part of this example is that defining a foreign key constraint on the child table allowed us to automatically delete all of the comments related to a specific blog post when this post was removed. You'll understand this better from the example, so here are the respective definitions of the mentioned tables, which establish a one-to-many relationship with each other:

DROP TABLE IF EXISTS `test`.`blogs`;

CREATE TABLE `test`.`blogs` (

`id` INT(10) UNSIGNED AUTO_INCREMENT,

`title` TEXT,

`content` TEXT,

`author` VARCHAR(45) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

DROP TABLE IF EXISTS `test`.`comments`;

CREATE TABLE `test`.`comments` (

`id` INT(10) UNSIGNED AUTO_INCREMENT,

`blog_id` INT(10) UNSIGNED DEFAULT NULL,

`comment` TEXT,

`author` VARCHAR(45) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `blog_ind` (`blog_id`),

CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Apart from defining some simple fields on the two above InnoDB tables, the previous code sample uses a foreign key constraint to allow a cascading update of the rows of the “comments” table whenever the “id” key of the parent is updated too.

The line that defines the constraint on the “id” parent field is the following:

CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON UPDATE CASCADE

Aside from specifying a cascading update on the child table based on the operations performed on the parent, the InnoDB engine supports other actions. These include “NO ACTION” and “RESTRICT,” which will refuse any update or deletion on the parent table.

Now, returning to the previous example, and based on the definition of the sample MySQL tables, if they were populated with the following data:

INSERT INTO blogs (id, title, content, author) VALUES (NULL,'Title of the first blog entry', 'Content of the first blog entry', 'Alejandro Gervasio')

 

INSERT INTO comments (id, blog_id, comment, author) VALUES (NULL, 1, 'Commenting first blog entry', 'Susan Norton'), (NULL, 1, 'Commenting first blog entry', 'Mary Wilson')

Then, if for any reason it’s necessary to update the first blog entry, automatically all of its related comments will be updated also, simply by running the following SQL statement:

"UPDATE blogs SET id = 2, title = 'Title of the first blog entry', content = 'Content of the first blog entry', author = 'John Doe' WHERE id = 1"

That was pretty neat, right? As I explained before, foreign key constraints permit you to delegate the maintenance of relationships between tables at the database level, which means writing less code for the application that interacts with the data layer.

In addition, it’s important to note that it’s possible to trigger deletions in a cascade, in a way similar to the case illustrated a moment ago. Thus, in the following section I’m going to reuse the two sample tables defined earlier to demonstrate how useful a foreign key constraint can be for deleting the comments related to a particular blog post when this entry is removed.

To see how this brand new example will be developed, click on the link shown below and keep reading.



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: