如何使用mysql的触发器实现外键约束

在mysql数据库中，有时会用到外键约束FOREIGN KEY。这些外键约束也可以使用触发器TRIGGER来实现。当然，外键约束和触发器都是不提倡使用的。因为外键约束和触发器容易给数据库服务器增加额外的负担，造成性能下降。甚至可能造成频发的锁等待或者死锁。

下边举例说明如何用触发器来实现外键约束，包括外键级联更新和删除。

假设有students和scores两张表，并且存在外键约束关系：

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `ID` varchar(64) COLLATE utf8mb4_bin NOT NULL,
  `NAME` varchar(64) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY(`NAME`)
) ENGINE=SequoiaDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO students (ID,NAME) values ('0001','张三');
INSERT INTO students (ID,NAME) values ('0002','李四');

DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores` (
  `NAME` varchar(64) COLLATE utf8mb4_bin NOT NULL,
  `SCORE` int(11) DEFAULT NULL,
  CONSTRAINT `CK_FKEY` FOREIGN KEY (`NAME`) REFERENCES `students` (`NAME`)  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=SequoiaDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO scores (NAME,SCORE) values ('张三',82);
INSERT INTO scores (NAME,SCORE) values ('李四',90);
/*插入一条不符合外键约束的记录*/
INSERT INTO scores (NAME,SCORE) values ('王五',60);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`scores`, CONSTRAINT `CK_FKEY` FOREIGN KEY (`NAME`) REFERENCES `students` (`NAME`) ON DELETE CASCADE ON UPDATE CASCADE)

上边的外键约束可以改为触发器，具体方法如下：
 

CREATE DATABASE IF NOT EXISTS test;
USE test;

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `ID` varchar(64) COLLATE utf8mb4_bin NOT NULL,
  `NAME` varchar(64) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY(`ID`),
  UNIQUE KEY(`NAME`)
) ENGINE=SequoiaDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

/*创建触发器，实现外键约束的级联更新效果*/
DELIMITER $
DROP TRIGGER IF EXISTS trg_cascade_update_students $
CREATE TRIGGER `trg_cascade_update_students` AFTER UPDATE ON `students` FOR EACH ROW 
BEGIN  
    /*当students.name字段值被更新，则需要更新socres.name字段*/
    UPDATE scores s SET s.name=NEW.name WHERE s.name=OLD.name;
END;
$
DELIMITER ;

/*创建触发器，实现外键约束的级联删除效果*/
DELIMITER $
DROP TRIGGER IF EXISTS trg_cascade_delete_students $
CREATE TRIGGER `trg_cascade_delete_students` AFTER DELETE ON `students` FOR EACH ROW 
BEGIN  
    /*当students有记录被删除，则需要删除socres表中相应记录*/
    DELETE FROM scores WHERE name=OLD.name;
END;
$
DELIMITER ;



DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores` (
  `NAME` varchar(64) COLLATE utf8mb4_bin NOT NULL,
  `SCORE` int(11) DEFAULT NULL
) ENGINE=SequoiaDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


/*创建触发器，实现插入数据时的外键约束效果*/
DELIMITER $
DROP TRIGGER IF EXISTS trg_constraint_insert_scores $
CREATE TRIGGER `trg_constraint_insert_scores` BEFORE INSERT ON `scores` FOR EACH ROW 
BEGIN  
    DECLARE returned_rows INT(11);
    DECLARE msg VARCHAR(1000);
    /*有记录插入scores表之前，需要到students表中去检查scores.name字段值在students.name字段中是否存在*/
    SELECT count(s.name) INTO returned_rows FROM students s WHERE s.name=NEW.name;
    IF returned_rows <> 1 THEN  
        set msg = "Cannot add or update a child row: a foreign key constraint on scores(name) fails.";
        SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = msg;  /*从mysql 5.5版本开始提供了SIGNAL方法，可以实现中断操作*/
    END IF;
END;
$

DELIMITER ;
/*创建触发器，实现更新数据时的外键约束效果*/
DELIMITER $
DROP TRIGGER IF EXISTS trg_constraint_update_scores $
CREATE TRIGGER `trg_constraint_update_scores` BEFORE UPDATE ON `scores` FOR EACH ROW 
BEGIN  
    DECLARE returned_rows INT(11);
    DECLARE msg VARCHAR(1000);
    /*在更新scores表之前，需要到students表中去检查scores.name字段值在students.name字段中是否存在*/
    SELECT count(s.name) INTO returned_rows FROM students s WHERE s.name=NEW.name;
    IF returned_rows <> 1 THEN  
        set msg = "Cannot add or update a child row: a foreign key constraint on scores(name) fails.";
        SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = msg;  /*从mysql 5.5版本开始提供了SIGNAL方法，可以实现中断操作*/
    END IF;
END;
$
DELIMITER ;

/*初始化两条记录*/
INSERT INTO students (ID,NAME) values ('0001','张三');
INSERT INTO students (ID,NAME) values ('0002','李四');
INSERT INTO scores (NAME,SCORE) values ('张三',82);
INSERT INTO scores (NAME,SCORE) values ('李四',90);

/*验证插入时的外键约束效果，预期会报错，无法插入这条数据*/
INSERT INTO scores (NAME,SCORE) values ('王五',60);
SELECT * FROM scores;
SELECT * FROM students;

/*验证更新时的外键约束效果，预期会报错，无法更新这条数据*/
UPDATE scores SET name='王五' WHERE name='张三';
SELECT * FROM scores;
SELECT * FROM students;

/*验证更新时的外键级联更新效果*/
UPDATE students SET name='赵六' WHERE name='张三';
SELECT * FROM scores;
SELECT * FROM students;

/*验证删除时的外键级联删除效果*/
DELETE FROM students WHERE name='李四';
SELECT * FROM scores;
SELECT * FROM students;

执行效果如下：
 

mysql> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> USE test;
Database changed
mysql> 
mysql> DROP TABLE IF EXISTS `scores`;
Query OK, 0 rows affected (0.02 sec)
mysql> DROP TABLE IF EXISTS `students`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `students` (
    ->   `ID` varchar(64) COLLATE utf8mb4_bin NOT NULL,
    ->   `NAME` varchar(64) COLLATE utf8mb4_bin NOT NULL,
    ->   PRIMARY KEY(`ID`),
    ->   UNIQUE KEY(`NAME`)
    -> ) ENGINE=SequoiaDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> /*创建触发器，实现外键约束的级联更新效果*/
mysql> DELIMITER $
mysql> DROP TRIGGER IF EXISTS trg_cascade_update_students $
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TRIGGER `trg_cascade_update_students` AFTER UPDATE ON `students` FOR EACH ROW 
    -> BEGIN  
    ->     /*当students.name字段值被更新，则需要更新socres.name字段*/
    ->     UPDATE scores s SET s.name=NEW.name WHERE s.name=OLD.name;
    -> END;
    -> $
Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER ;
mysql> 
mysql> 
mysql> 
mysql> /*创建触发器，实现外键约束的级联删除效果*/
mysql> DELIMITER $
mysql> DROP TRIGGER IF EXISTS trg_cascade_delete_students $
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TRIGGER `trg_cascade_delete_students` AFTER DELETE ON `students` FOR EACH ROW 
    -> BEGIN  
    ->     /*当students有记录被删除，则需要删除socres表中相应记录*/
    ->     DELETE FROM scores WHERE name=OLD.name;
    -> END;
    -> $
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
mysql> 

mysql> CREATE TABLE `scores` (
    ->   `NAME` varchar(64) COLLATE utf8mb4_bin NOT NULL,
    ->   `SCORE` int(11) DEFAULT NULL
    -> ) ENGINE=SequoiaDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> 
mysql> /*创建触发器，实现插入数据时的外键约束效果*/
mysql> DELIMITER $
mysql> DROP TRIGGER IF EXISTS trg_constraint_insert_scores $
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TRIGGER `trg_constraint_insert_scores` BEFORE INSERT ON `scores` FOR EACH ROW 
    -> BEGIN  
    ->     DECLARE returned_rows INT(11);
    ->     DECLARE msg VARCHAR(1000);
    ->     /*有记录插入scores表之前，需要到students表中去检查scores.name字段值在students.name字段中是否存在*/
    ->     SELECT count(s.name) INTO returned_rows FROM students s WHERE s.name=NEW.name;
    ->     IF returned_rows <> 1 THEN  
    ->         set msg = "Cannot add or update a child row: a foreign key constraint on scores(name) fails.";
    ->         SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = msg;  /*从mysql 5.5版本开始提供了SIGNAL方法，可以实现中断操作*/
    ->     END IF;
    -> END;
    -> $
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> DELIMITER ;
mysql> /*创建触发器，实现更新数据时的外键约束效果*/
mysql> DELIMITER $
mysql> DROP TRIGGER IF EXISTS trg_constraint_update_scores $
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TRIGGER `trg_constraint_update_scores` BEFORE UPDATE ON `scores` FOR EACH ROW 
    -> BEGIN  
    ->     DECLARE returned_rows INT(11);
    ->     DECLARE msg VARCHAR(1000);
    ->     /*在更新scores表之前，需要到students表中去检查scores.name字段值在students.name字段中是否存在*/
    ->     SELECT count(s.name) INTO returned_rows FROM students s WHERE s.name=NEW.name;
    ->     IF returned_rows <> 1 THEN  
    ->         set msg = "Cannot add or update a child row: a foreign key constraint on scores(name) fails.";
    ->         SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = msg;  /*从mysql 5.5版本开始提供了SIGNAL方法，可以实现中断操作*/
    ->     END IF;
    -> END;
    -> $
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
mysql> 
mysql> /*初始化两条记录*/
mysql> INSERT INTO students (ID,NAME) values ('0001','张三');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO students (ID,NAME) values ('0002','李四');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO scores (NAME,SCORE) values ('张三',82);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO scores (NAME,SCORE) values ('李四',90);
Query OK, 1 row affected (0.02 sec)

mysql> 
mysql> /*验证插入时的外键约束效果，预期会报错，无法插入这条数据*/
mysql> INSERT INTO scores (NAME,SCORE) values ('王五',60);
ERROR 1644 (23000): Cannot add or update a child row: a foreign key constraint on scores(name) fails.
mysql> SELECT * FROM scores;
+--------+-------+
| NAME   | SCORE |
+--------+-------+
| 张三   |    82 |
| 李四   |    90 |
+--------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM students;
+------+--------+
| ID   | NAME   |
+------+--------+
| 0001 | 张三   |
| 0002 | 李四   |
+------+--------+
2 rows in set (0.01 sec)

mysql> 
mysql> /*验证更新时的外键约束效果，预期会报错，无法更新这条数据*/
mysql> UPDATE scores SET name='王五' WHERE name='张三';
ERROR 1644 (23000): Cannot add or update a child row: a foreign key constraint on scores(name) fails.
mysql> SELECT * FROM scores;
+--------+-------+
| NAME   | SCORE |
+--------+-------+
| 张三   |    82 |
| 李四   |    90 |
+--------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM students;
+------+--------+
| ID   | NAME   |
+------+--------+
| 0001 | 张三   |
| 0002 | 李四   |
+------+--------+
2 rows in set (0.01 sec)

mysql> 
mysql> /*验证更新时的外键级联更新效果*/
mysql> UPDATE students SET name='赵六' WHERE name='张三';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM scores;
+--------+-------+
| NAME   | SCORE |
+--------+-------+
| 赵六   |    82 |
| 李四   |    90 |
+--------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM students;
+------+--------+
| ID   | NAME   |
+------+--------+
| 0001 | 赵六   |
| 0002 | 李四   |
+------+--------+
2 rows in set (0.00 sec)

mysql> 
mysql> /*验证删除时的外键级联删除效果*/
mysql> DELETE FROM students WHERE name='李四';
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM scores;
+--------+-------+
| NAME   | SCORE |
+--------+-------+
| 赵六   |    82 |
+--------+-------+
1 row in set (0.01 sec)

mysql> SELECT * FROM students;
+------+--------+
| ID   | NAME   |
+------+--------+
| 0001 | 赵六   |
+------+--------+
1 row in set (0.00 sec)

mysql>