如何在MySql中使用触发器来创建外键 [英] How to use trigger in MySql to make foreign key

问题描述

我想使用触发器在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.com/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: https://dev.mysql.com/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.

这篇关于如何在MySql中使用触发器来创建外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！