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

Deleting database records without foreign key constraints - 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

To prove how helpful foreign key constraints can be for maintaining the integrity of a database when rows of a parent table are deleted, Iím going to recreate the example that you learned in the previous section, but this time using MyISAM tables.

So, first Iím going to define the tables, a basic process accomplished by the following SQL code:

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=MyISAM 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`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

So far, thereís nothing special with reference to the way that the two sample tables have been defined. However, you must notice that they use the default MyISAM database engine, therefore foreign key constraints wonít be supported (at least at the time of this writing).

Having defined the tables that comprise the data layer of a basic blog application, itís time to populate them with some fictional data. Hereís the SQL code that does that:

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')

Essentially, what the above code snippet does is insert a blog post in the ďblogsĒ table, and two comments in the child table, thus simulating that the sample blog application is up and running. Now, if my first post was deleted, then its related comments should be removed as well.

But how could this be done? Well, the following SQL statements would do the task pretty roughly:

DELETE FROM blogs WHERE id = 1

DELETE FROM comments WHERE blog_id = 1

Naturally, in a real-world scenario, these two DELETE statements should be performed by a server-side language like PHP, Python or Ruby, and not using raw SQL, but for example purposes, these are good enough.

So far, so good. At this point Iím certain that you grasped the logic involved in deleting a blog entry, along with its associated comments, when using MyISAM tables. So, next I'll rebuild the same example, but this time the tables will be defined by utilizing the InnoDB storage engine and a simple foreign key constraint.

This example will be set up in the last section of this tutorial. Therefore, jump ahead and read the next few lines.



 
 
>>> 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: