sql级联更新和级联删除

---

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/