MySQL：使用cmd或者navicat创建外键失败情形以及原因分析

最新推荐文章于 2023-07-05 10:36:16 发布

大巧不工

最新推荐文章于 2023-07-05 10:36:16 发布

阅读量533

点赞数 1

分类专栏：实战心得

实战心得专栏收录该内容

4 篇文章

订阅专栏

转载自：https://www.2cto.com/database/201708/670413.html

一、设置存储引擎

1.查看当前存储引擎

1	`mysql> show engines; //查看所有的存储引擎`

结果如下所示：
说明：我当前的数据表设置的存储引擎是InnoDB形式的

2.在数据库配置文件my.ini中修改默认存储引擎

方法一：如果当前存储引擎为Myisam形式，需要将存储引擎修改一下，在这里建议大家可以在MySQL的配置文件里面进行修改

2.2 修改完配置文件后，需要重启数据库服务器

方法二：使用alter语句修改存储引擎

1	`mysql> show` `create` `table` `tb_dept; //查看tb_dept的建表语句`

1	`mysql> show` `create` `table` `tb_emp;//查看tb_emp的建表语句`

结果如下所示：

说明我这里已经是InnoDB的模式

1	`mysql>` `alter` `table` `tb_dept engine innodb; //将tb_dept表的存储引擎改成innodb模式`

1	`mysql>` `alter` `table` `tb_emp engine innodb; //将tb_emp表的存储引擎改成innodb模式`

方法一与方法二比较

方法一更彻底所有的数据表都会默认innodb模式方法二治标不治本只有设置的数据表会是innodb模式注意：主表和从表均要修改成innodb模式

二、创建测试SQL脚本并运行

1.编写sql脚本

-- 主表：部门表

create table tb_dept(

id int unsigned auto_increment primary key, -- 部门编号

name varchar(20) not null default '' -- 部门名字

);

-- 从表：员工表

create table tb_emp(

id int unsigned auto_increment primary key, -- 员工编号

name varchar(20) not null default '' , -- 员工姓名

dept_id int, -- 部门编号

constraint constraint_name1 foreign key dept_id references tb_dept (id) -- 添加外键约束方法一

);

-- 添加外键约束方法二

alter table tb_dept add constraint constraint_name2 foreign key dept_id references tb_dept (id);

说明：

consraint 表示约束

constraint_name 表示自定义约束的名字若不写，则自动生成一个约束名

foreign key 表示外键

references 表示参照

2.运行
结果如下：
不论是在命令行里还是在navicat图形界面化客户端里执行都是报这个错，在百度里剖了这个错误，查到的解决办法都不适用
1）从表被约束字段的数据类型要和主表中的约束字段一样 2）主表中的约束字段是主表的主键或者唯一键或者索引 3）存储引擎一开始就设置为InnoDB模式，其他不再赘述题外话： InnoDB 支持外键 Myisam 不支持外键
最后经过排查发现是因为tb_dept表中id设置了unsigned，tb_emp表中dept_id并没有设置unsigned; 血淋淋的教训严重受到 SQL Server 的影响花了一个多小时的时间终于发现。。。。。。都是泪啊~~~~(>_<)~~~~
MySQL在很多小细节上与SQL Server不一样
tb_dept(id) 设置auto_increment tb_emp(dept_id)可以不设置 tb_dept(id) 为主键必然不空 tb_emp(dept_id)可以不设置not null
最终结果给tb_emp(dept_id)加上unsigned后一切正常