mysql 创建外键引用时眼瞎了，然而mysql 报的错也是认人摸不着头脑

最新推荐文章于 2024-03-09 16:01:46 发布

weixin_30294295

最新推荐文章于 2024-03-09 16:01:46 发布

阅读量69

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/JiangLe/p/5807099.html

本文介绍了在MySQL中创建外键约束失败的问题及其解决方法。错误提示为：无法创建外键约束，因为引用表中没有索引使得被引用列作为第一个列出现。问题在于主表和从表的数据类型不一致，通过将teacher_id字段的数据类型调整为int解决了该问题。

问题描述：

　　在创建外键约束时mysql 报 Create table 'tempdb/student' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.

这个问题是主表和从表在列上的数据类型不一致造成的

mysql> create table teacher
    -> (id int auto_increment primary key,
    -> name varchar(16) char set utf8
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> 
mysql> create table student
    -> (id int auto_increment primary key,
    -> name varchar(16) char set utf8,
    -> teacher_id varchar(16) char set utf8, -- 注意问题在这里 teahcer_id 的类型错了
    -> constraint foreign key fk_teacher_id (teacher_id) references teacher(id)
    -> );
ERROR 1215 (HY000): Cannot add foreign key constraint
Warning (Code 150): Create table 'tempdb/student' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.

Error (Code 1215): Cannot add foreign key constraint

改正：

只要数据类型调整过来就行了

mysql> create table teacher
    -> (id int auto_increment primary key,
    -> name varchar(16) char set utf8
    -> );
s teacher(id)
);Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> create table student
    -> (id int auto_increment primary key,
    -> name varchar(16) char set utf8,
    -> teacher_id int,
    -> constraint foreign key fk_teacher_id (teacher_id) references teacher(id)
    -> );
Query OK, 0 rows affected (0.01 sec)