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

问题描述：

　　在创建外键约束时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)

巴特西

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

最新文章

热门文章