Home arrow PHP arrow Page 2 - Working with Foreign Key Constraints in MySQL

When to use foreign key constraints - PHP

In this first part of a six-part series, I provide you with a basic introduction to using foreign key constraints with InnoDB tables in MySQL. As you'll soon see, triggering a cascading update on the records of a child table when updating the rows in a parent table is a breeze.

TABLE OF CONTENTS:
  1. Working with Foreign Key Constraints in MySQL
  2. When to use foreign key constraints
  3. Updating blog entries and maintaining database integrity
  4. Delegating update operations to the database
By: Alejandro Gervasio
Rating: starstarstarstarstar / 8
December 09, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Frankly speaking, it’s not mandatory to use foreign key constraints when working with InnoDB tables in MySQL. However, to demonstrate how useful they can be in certain cases, I’m going to translate to functional code the example mentioned at the beginning. It involves two MyISAM tables which will be used for storing some blog entries and the comments on them respectively.

With that database schema defined, I’m going to set up a one-to-many relationship between these tables by creating a foreign key on the table that houses the comments, thus tying them to a particular blog entry.

Having explained that, here’s the basic SQL code that roughly creates the sample MyISAM tables:

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;

Well, at this point things are starting to look a bit more interesting. I’ve just defined the two MyISAM tables that will comprise the data layers of the blog application that I plan to build progressively.

As you can see above, the first table, called “blogs,” is made up of a few intuitive fields that will be used for storing the ID of each blog entry, then its title and content, and finally the author that posted it. On the other hand, the “comments” table will hold the comments on each entry, and will use the entry’s ID as its foreign key, in this way setting up the aforementioned one-to-many relationship.

So far, nothing special is happening here, since creating two simple MyISAM tables like the ones shown previously should be pretty easy to grasp for you. So, next we'll populate those tables with some trivial records, so you can see what operations need to be performed on the “comments” table when one blog entry is deleted in the other one.

To learn the full details of this process, click on the link below and read the following section, please.



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