如何在MySql中使用触发器来创建外键

本文介绍了如何在MySql中使用触发器来创建外键的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想使用触发器在MySql中创建外键。我有以下表： 1）'content'表： teacher_id varchar（20） sub_id varchar（20） path varchar（100） file_name varchar （100）

2）'teacher'表： teacher_id varchar（20） teacher_name varchar（45）

并且我使用以下代码触发（分隔符//）：

CREATE TRIGGER fk_content_teacher_temp BEFORE INSERT ON`content` FOR EACH ROW BEGIN DECLARE has_row TINYINT; SET has_row = 0; SELECT 1 INTO has_row FROM`teacher` INNER JOIN`content` ON content.teacher_id = teacher.teacher_id; IF has_row = 0 THEN INSERT error_msg VALUES（'Foreign Key Constraint Violated！'）; END IF; END //

问题是，当我试图在内容表中插入一个我得到以下错误：

1172 - 结果由多行组成

我可以做什么使它工作正常，或任何其他方式，我可以使用触发器外键？ 提前谢谢！

解决方案

这是一个古老的问题，我想为未来的搜索者

在最近的一个项目中，我无法使用InnoDB，但必须使用MyISAM引擎（实际上是MariaDB的Aria引擎）对于包含外键的数据库传输。

我选择使用此处所述的触发器实现外键。

这里提供了一个很好的主题介绍： https： //dev.mysql/tech-resources/articles/mysql-enforcing-foreign-keys.html

但是，我将概述我的解决方案我发现一些东西在上面不是完全可行的我。例如。

对于演示，我使用下面的语句来更新父表。对于父表的任何更新在其限制示例中完全禁止，即使儿童没有受到影响。表定义和测试数据：

CREATE TABLE`__parent`（`id` int UNSIGNED NOT NULL AUTO_INCREMENT，PRIMARY KEY `））ENGINE =`Aria`; CREATE TABLE`__child`（`id` int UNSIGNED NOT NULL AUTO_INCREMENT，`parent_id` int UNSIGNED，PRIMARY KEY（`id`），INDEX`parent_id_idx`使用BTREE（`parent_id`））ENGINE =`Aria` ; INSERT INTO __parent VALUES（1），（2），（3）; INSERT INTO __child VALUES（1,1），（2,2），（3,1），（4,2），（5,3），（6,1）当没有对应的链接父条目时，

阻止插入

DELIMITER // CREATE TRIGGER __before_insert_child BEFORE INSERT ON __child FOR EACH ROW BEGIN IF（SELECT COUNT（*）FROM __parent WHERE __parent.id = new.parent_id）= 0 THEN SIGNAL SQLSTATE'45000'SET MYSQL_ERRNO = 30001，MESSAGE_TEXT ='不能插入记录。外部父键不存在！ END IF; END // DELIMITER;

阻止更新

DELIMITER // CREATE TRIGGER __before_update_child BEFORE UPDATE ON __child FOR EACH ROW BEGIN IF（SELECT COUNT（*）FROM __parent WHERE __parent.id = new.parent_id）= 0 THEN SIGNAL SQLSTATE'45000'SET MYSQL_ERRNO = 30001，MESSAGE_TEXT ='不能更新记录。外部父键不存在！ END IF; END // DELIMITER;更新父级时，

级联更新 p>

DELIMITER // CREATE TRIGGER __after_update_parent更新后__parent FOR每个行 BEGIN UPDATE __child SET __child.parent_id = new.id WHERE __child.parent_id = old.id; END // DELIMITER;删除父级时，

级联删除 p>

DELIMITER // CREATE TRIGGER __after_delete_parent后删除__parent FOR每个行 BEGIN DELETE FROM __child WHERE __child.parent_id = old.id; END; END // DELIMITER;

有时你不想级联但限制。在这种情况下，请改用以下内容：

限制父更新到子表：

DELIMITER // CREATE TRIGGER __before_update_parent BEFORE UPDATE ON __parent FOR EACH ROW BEGIN IF（old.id<> new.id AND（SELECT COUNT（*）FROM __child WHERE __child.parent_id = old.id）<> 0）THEN SIGNAL SQLSTATE'45000'SET MYSQL_ERRNO = 30001，MESSAGE_TEXT ='不能更新记录。对子表的外键更新限制！ END IF; END // DELIMITER;

限制父级删除：

DELIMITER // CREATE TRIGGER __before_delete_parent BEFORE DELETE ON __parent FOR EACH ROW BEGIN IF（SELECT COUNT（* ）FROM __child WHERE __child.parent_id = old.id）<> 0 THEN SIGNAL SQLSTATE'45000'SET MYSQL_ERRNO = 30001，MESSAGE_TEXT ='不能删除记录。外键存在于子表中！ END IF; END // DELIMITER;

希望这有助于某人。

I want to use trigger to make foreign key in MySql. I have the following tables: 1) 'content' table: teacher_id varchar(20) sub_id varchar(20) path varchar(100) file_name varchar(100)

2) 'teacher' table: teacher_id varchar(20) teacher_name varchar(45)

and I am using the following code for trigger(delimiter //):

CREATE TRIGGER fk_content_teacher_temp BEFORE INSERT ON `content` FOR EACH ROW BEGIN DECLARE has_row TINYINT; SET has_row = 0; SELECT 1 INTO has_row FROM `teacher` INNER JOIN `content` ON content.teacher_id=teacher.teacher_id; IF has_row=0 THEN INSERT error_msg VALUES ('Foreign Key Constraint Violated!'); END IF; END//

The problem is, when am trying to insert in content table for a teacher_id which is not present in teacher table, I get the following error:

1172 - Result consists of more than one row

What can I do to make it work fine, or any other way i can use trigger to make foreign keys? Thank you in advance!

解决方案

While this is an oldish question I would like to provide some insight for future searchers on how one might deal with such issue.

In a recent project I was unable to use InnoDB but had to use the MyISAM engine (in reality it was MariaDB's Aria engine) for a database transfer which contained foreign keys.

I opted for implementing foreign keys using triggers as described here.

A great intro into the subject is provided here: dev.mysql/tech-resources/articles/mysql-enforcing-foreign-keys.html

However, I will outline my solution as I found some thing not fully workable for me in the above. E.g. Any update to a parent table was completely prohibited in their "restrict" example when a foreign child key existed even though the child was not affected.

For demonstration I use the following table definitions and test data:

CREATE TABLE `__parent` (`id` int UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=`Aria`; CREATE TABLE `__child` (`id` int UNSIGNED NOT NULL AUTO_INCREMENT,`parent_id` int UNSIGNED, PRIMARY KEY (`id`), INDEX `parent_id_idx` USING BTREE (`parent_id`) ) ENGINE=`Aria`; INSERT INTO __parent VALUES (1), (2), (3); INSERT INTO __child VALUES (1,1), (2,2), (3,1), (4,2), (5,3), (6,1);

Prevent inserts into a child table when no corresponding linked parent entry exists:

DELIMITER // CREATE TRIGGER __before_insert_child BEFORE INSERT ON __child FOR EACH ROW BEGIN IF (SELECT COUNT(*) FROM __parent WHERE __parent.id=new.parent_id) = 0 THEN SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO = 30001, MESSAGE_TEXT = 'Can\'t insert record. Foreign parent key does not exist!'; END IF; END // DELIMITER ;

Prevent updates to a child table where it would unlink a child record:

DELIMITER // CREATE TRIGGER __before_update_child BEFORE UPDATE ON __child FOR EACH ROW BEGIN IF (SELECT COUNT(*) FROM __parent WHERE __parent.id = new.parent_id) = 0 THEN SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO = 30001, MESSAGE_TEXT = 'Can\'t update record. Foreign parent key does not exist!'; END IF; END // DELIMITER ;

Cascading updates to the child table when the parent is updated:

DELIMITER // CREATE TRIGGER __after_update_parent AFTER UPDATE ON __parent FOR EACH ROW BEGIN UPDATE __child SET __child.parent_id=new.id WHERE __child.parent_id=old.id; END // DELIMITER ;

Cascade deletes to the child table when a parent is deleted:

DELIMITER // CREATE TRIGGER __after_delete_parent AFTER DELETE ON __parent FOR EACH ROW BEGIN DELETE FROM __child WHERE __child.parent_id=old.id; END; END // DELIMITER ;

Sometime you don't want to cascade but restrict. In this case use the following instead:

Restrict parent updates to the child table:

DELIMITER // CREATE TRIGGER __before_update_parent BEFORE UPDATE ON __parent FOR EACH ROW BEGIN IF ( old.id <> new.id AND (SELECT COUNT(*) FROM __child WHERE __child.parent_id = old.id) <> 0 ) THEN SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO = 30001, MESSAGE_TEXT = 'Can\'t update record. Foreign key updates to child table restricted!'; END IF; END // DELIMITER ;

Restrict parent deletes from the child table:

DELIMITER // CREATE TRIGGER __before_delete_parent BEFORE DELETE ON __parent FOR EACH ROW BEGIN IF ( SELECT COUNT(*) FROM __child WHERE __child.parent_id = old.id) <> 0 THEN SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO = 30001, MESSAGE_TEXT = 'Can\'t delete record. Foreign key exists in child table!'; END IF; END // DELIMITER ;

Hope this helps someone.