mysql外键

指定外键,注意，表的存储引擎药选择支持外键的存储引擎，不然无效

create table `category`
(
`category_id` int unsigned primary key not null,
`category_name` char(10)
)engine=innodb charset=utf8;
create table `book`
(
`book_id` int unsigned primary key,
`book_name` char(10),
`category_id` int unsigned not null,
constraint fk_1 foreign key(`category_id`) references `category`(`category_id`)#指定 `category` 表的 `category_id` 字段为 当前表的 `category_id` 字段的外键
) engine=innodb charset=utf8;

如果插入进 book 表的数据的 category_id 不在 category 表的中 category_id 字段中，就会报如下的错误

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`book`, CONSTRAINT `fk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`))

如果直接删除外键表的话会报错

mysql> drop table if exists `category`;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

需要先删除创建了外键约束的表，或者删除外键约束

drop table if exists `book`;
drop table if exists `category`;