Parkside Web Development

I’ve been using MySQL for quite some time and, I’ve gotten used to managing data integrity via php code. But, regardless how careful you are there are some conditions that can cause invalid data.

One of the most common issues that arises is orphaned records. This can occur when you delete a parent record and for some reason the child records don’t get removed. It can also occur if the parent record has it’s primary key field updated and it’s child records don’t have their foreign keys updated. With MySQL 5 that doesn’t have to happen anymore. Using a constraint with cascading your data is kept synchronized.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE TABLE useraccount (
  user_id bigint(22) NOT NULL auto_increment COMMENT 'Primary key',
  fname varchar(50) NOT NULL COMMENT 'Users first name',
  ...
  PRIMARY KEY  (siteuser_id)
) ENGINE=InnoDB;
 
CREATE TABLE purchaseorder (
  order_id bigint(22) NOT NULL auto_increment COMMENT 'Primary key',
  user_id bigint(22) NOT NULL default '0' COMMENT 'Foreign Key',
  purchasedate timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'Date Purchased',
  ...
  PRIMARY KEY  (order_id),
  KEY `user_id_key` (`user_id`)
) ENGINE=InnoDB;

It’s pretty clear that we’ve got a one to many relation ship with the useraccount table as the parent and purchaseorder as the child. At this point you could start adding orphan data to the purchase order table and not receive any type of warning or error. We’ll fix that by adding a foreign key constraint.

1
2
3
4
5
6

ALTER TABLE `purchaseorder`
  ADD CONSTRAINT `purchaseorder_constraint`
  FOREIGN KEY (`user_id`)
  REFERENCES `useraccount` (`user_id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

Once the constraint is in place MySQL will prevent you from creating orphan records. So if we ran this code:

1

INSERT INTO purchaseorder SET user_id = -1;

We would receive the error…

Cannot add or update a child row: a foreign key constraint fails…

Now if we were to add a parent record and a couple of child records.

1
2
3
4
5
6

INSERT INTO useraccount SET fname = 'Rob';
 
// assuming our first record is user_id = 1
INSERT INTO purchaseorder SET user_id = 1;
INSERT INTO purchaseorder SET user_id = 1;
INSERT INTO purchaseorder SET user_id = 1;

Then we delete the parent record and look for the child records.

1
2
3

DELETE FROM useraccount WHERE user_id = 1;
 
SELECT * FROM purchaseorder;

Our result is just what we want but without any coding whatsoever.

Empty set