Triggers and MySQL.

Simulating Foreign Key Constraints with triggers.

MyISAM does not have any key constraints. This means that you can't cascade deletes and updates to other tables as you would normally do. You can however simulate the Foreign Key Constraints using triggers.

Say you've got the following 2 tables in mysql:

CREATE TABLE Role(
	id	bigint,
	name	varchar(255),
	PRIMARY KEY(id)
	);

CREATE TABLE User(
	id	bigint,
	name	varchar(255),
	role_id	bigint,
	PRIMARY KEY(id)
	);

Let's say we've got 2 users: John and Pete
John is a Site Admin and Pete is a Site Moderator
And of course they are both Power Users

INSERT INTO Role(id,name) VALUES (1,'Site Admin'), (2,'Site Moderator'), (3,'Power User');
INSERT INTO User(id,name,role_id) VALUES (1,'John',1), (2,'John',3), (3,'Pete',2),
(4,'Pete',3);

So what is it that we want to accomplish?
1. If you insert a User with a role_id that doesn't exist we want to see an error: INSERT: non-existing role_id.
2. If you change a role_id in the Role-table we want the User-table automatcally updated to the new role_id.
3. If you delete a role from the Role-table that is still used in the User-table, the corresponding rows from the User-table are deleted.

There is one difficulty though; the errors.
You can't create your own errors in MySQL, but there is a trick you can use:
First you create an error-table:

CREATE TABLE error_messages(
	error_message VARCHAR(65),
	PRIMARY KEY(error_message)
);
INSERT INTO error_messages VALUES ('INSERT: non-existing role_id');

because error_message is a primary key if you insert the same error again this will create you're custom error:

mysql> INSERT INTO error_msg VALUES ('INSERT: non-existing role_id');
ERROR 1062 (23000): Duplicate entry 'INSERT: non-existing role_id' for key 1

Ok, it's not a real error but it shows you what you need to know.

The first trigger will look like this:

DELIMITER |
CREATE TRIGGER insert_user
BEFORE INSERT
ON User
FOR EACH ROW BEGIN
	IF (select count(*) from role where id = new.role_id) = 0 THEN
	insert error_messages values ("INSERT: non-existing role_id");
	END IF;
END;|
DELIMITER ;

Before you insert, it counts the number of Roles with the role_id you want to use in your insert statement.
If this is 0 an error will occur and the insert will not be done.
If this is not 0 then the insert will be done.

The second trigger just updates the role id from OLD.role_id to NEW.role_id.
For this trigger there is no error-message.

DELIMITER |
CREATE TRIGGER update_cascade
AFTER UPDATE
ON Role
FOR EACH ROW BEGIN
	UPDATE User SET role_id=NEW.id WHERE role_id = OLD.id;
END;|
DELIMITER ;

The final trigger just deletes the rows from the User-table with a role_id you want to delete from the Role-table.

DELIMITER |
CREATE TRIGGER delete_cascade
AFTER DELETE
ON Role
FOR EACH ROW BEGIN
	DELETE FROM User WHERE role_id = OLD.id;
END;|
DELIMITER ;

You could also choose to restrict the delete; you will only allow a delete if there are no more users with that particular Role: (Don't forget to insert the error in the error-table!)

DELIMITER |
CREATE TRIGGER delete_restrict
BEFORE DELETE
ON Role
FOR EACH ROW BEGIN
	IF (select count(*) from User where role_id = OLD.id) > 0 THEN
	insert error_messages values ("DELETE: role_id still exists in User");
	END IF;
END;|
DELIMITER ;

Or also you could set the role_id to NULL in the user-table:

DELIMITER |
CREATE TRIGGER delete_null
AFTER DELETE
ON Role
FOR EACH ROW BEGIN
	UPDATE User SET role_id = NULL WHERE role_id = OLD.id;
END;|
DELIMITER ;

And that's all there is to it ;-)

The final thing is testing it all:

mysql> select * from user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  1 | John |       1 |
|  2 | John |       3 |
|  3 | Pete |       2 |
|  4 | Pete |       3 |
+----+------+---------+
4 rows in set (0.00 sec)

mysql> select * from role;
+----+----------------+
| id | name           |
+----+----------------+
|  1 | Site Admin     |
|  2 | Site Moderator |
|  3 | Power User     |
+----+----------------+
3 rows in set (0.00 sec)

mysql>

First we do the update on the role-table:

mysql> update role set id = 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from  user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  1 | John |      10 |
|  2 | John |       3 |
|  3 | Pete |       2 |
|  4 | Pete |       3 |
+----+------+---------+
4 rows in set (0.00 sec)

It works !
Now we inert a user with a role_id that doen't exist.

mysql> insert into user values(5,'Me',1);
ERROR 1062 (23000): Duplicate entry 'INSERT: non-existing role_id' for key 1
mysql>

This is correct again because there is no more Role with id = 1 (just changed it to 10!)
Finally we do the delete:

mysql> delete from role where id = 10;
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  2 | John |       3 |
|  3 | Pete |       2 |
|  4 | Pete |       3 |
+----+------+---------+
3 rows in set (0.00 sec)

mysql>

So it cascades down to the user-table just as we wanted.
Finally we check the delete_restrict-trigger:

mysql> delete from role where id = 2;
ERROR 1062 (23000): Duplicate entry 'DELETE: role_id still exists in User' for k
ey 1
mysql>

And also delete_null-trigger:

mysql> delete from role where id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  2 | John |       3 |
|  3 | Pete |    NULL |
|  4 | Pete |       3 |
+----+------+---------+
3 rows in set (0.00 sec)

mysql>