title: sql级联更新和级联删除
date: 2019-05-27 23:15:29
tags: [SQL, Mysql]
sql级联更新
执行更新book表失败
后来查找原因是book表被borrow引用了,所以数据库不知道book表更新borrow表要不要更新,所以我们需要进行级联更新,也就是book表更新,borrow表和其它引用了book表也更新外键。
book表
borrow表
级联更新
在创建表时就指定级联更新和级联删除
CREATE TABLE `book` (
`ISBN` varchar(20) NOT NULL,
`book_name` varchar(50) NOT NULL,
`book_author` varchar(20) NOT NULL,
`book_pub` varchar(50) DEFAULT NULL,
`sort_id` int(11) DEFAULT NULL,
`book_record` varchar(255) DEFAULT NULL,
`book_price` decimal(10,0) NOT NULL,
`book_borrow` int(11) DEFAULT '0',
PRIMARY KEY (`ISBN`),
KEY `sort_id` (`sort_id`),
CONSTRAINT `book_ibfk_1` FOREIGN KEY (`sort_id`) REFERENCES `book_sort` (`sort_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `borrow` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ISBN` varchar(20) NOT NULL,
`s_id` varchar(20) NOT NULL,
`price` decimal(10,0) NOT NULL,
`book_name` varchar(50) NOT NULL,
`borrow_date` varchar(255) DEFAULT NULL,
`expect_return_date` varchar(255) DEFAULT NULL,
`return_date` varchar(255) DEFAULT NULL,
`book_borrow` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `s_id` (`s_id`),
KEY `ISBN` (`ISBN`),
CONSTRAINT `borrow_ibfk_1` FOREIGN KEY (`s_id`) REFERENCES `student` (`s_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `borrow_ibfk_2` FOREIGN KEY (`ISBN`) REFERENCES `book` (`ISBN`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
如果是已经创建了数据表,那么先撤销外键约束
ALTER TABLE borrow
DROP FOREIGN KEY fk_borrow1 这里为你外键名比如constraint后面的名称
然后再重新增加约束
ALTER TABLE Orders
ADD CONSTRAINT fk_borrow1
FOREIGN KEY (s_id)
REFERENCES student(s_Id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Orders
ADD CONSTRAINT fk_borrow2
FOREIGN KEY (ISBN)
REFERENCES book(ISBN) ON DELETE CASCADE ON UPDATE CASCADE;
最后再执行更新
UPDATE book SET ISBN = '201905152321007', book_name = 'javascript', book_author = 'javascript作者', book_pub = 'javascript出版社', book_borrow = 1, sort_id = 1, book_record = '2019-05-24', book_price = 250 WHERE ISBN = '201905152321001';
即可成功更新book表,而borrow表中的ISBN也跟着更新。
更多文章请查阅我的博客:https://zhouhangzooo.github.io/