MySQL不用Cascade Delete修改子表和safe mode
mysql支持级联操作,但是并非所有DBMS都支持。所以不用cascade设计这样的存储过程主要有两种方法:
- 删除原有的外键约束,对被参照或参照表修改,之后再将外键约束加回去。这个方法的好处是简单好理解,但是有的设备上或者数据库并不能使用。
- 将被参照表中要修改的部分记录下来后,增加一条要修改的内容,之后就可以正常操作参照表了。最后删除旧内容即可。
本人采用的就是后一种方法。举例如下方。Book表中的ID为主键,Borrow表中Book_ID是外键,参照了Book中的ID。想要修改Book中某一本书的ID,可以使用下列存储过程:
#设计一个存储过程,实现对 Book 表的 ID 的修改(本题要求不得使用外键定义时的
#on update cascade 选项,因为该选项不是所有 DBMS 都支持)。
DELIMITER //
DROP PROCEDURE IF EXISTS ChangeBookID;
CREATE PROCEDURE ChangeBookID(IN oldID char(8), IN newID char(8), OUT message varchar(10))
BEGIN
DECLARE bname varchar(20);
DECLARE bauthor varchar(10);
DECLARE bprice float;
DECLARE bstatus int;
SELECT name, author, price, status
FROM Book
WHERE ID = oldID
INTO bname, bauthor, bprice, bstatus;
INSERT INTO Book VALUE (newID, bname, bauthor, bprice, bstatus);
UPDATE Borrow SET book_ID = newID WHERE book_ID = oldID;
DELETE FROM Book WHERE ID = oldID;
SET message = 'SUCCESS!';
END //
DELIMITER ;
MySQL workbench默认开启安全模式(safe mode),也就是说如果一次性修改多行,workbench会告诉你因为安全模式(一次只修改一行比较安全)所以操作不允许。可以在preferences->SQL Editor->Safe Updates中关闭。