无法添加外键约束，（＃1215 - 无法添加外键约束）

I'm trying to create a database, i have one table in my database and want to add the other one.

我正在尝试创建一个数据库，我的数据库中有一个表，并希望添加另一个表。

The table i have:

我有的表：

create table department(
    dept_name varchar(20),
    building varchar(20) not null,
    budget numeric(8,2),
    primary key (dept_name)
)

and i want to add this table:

我想添加这个表：

create table student (
    ID varchar(5),
    name varchar(20) not null,
    dept_name varchar(20),
    tot_cred numeric(3,0),
    primary key (ID),
    foreign key (dept_name) references department
)

But i get this error:

但我得到这个错误：

#1215 - Cannot add foreign key constraint

Can you guys help me with this problem? Thanks.

你能帮帮我解决这个问题吗？谢谢。

1 个解决方案

#1

Your 2nd table should be-

你的第二张桌应该是 -

create table student (
    ID varchar(5),
    name varchar(20) not null,
    dept_name varchar(20),
    tot_cred numeric(3,0),
    primary key (ID),
    foreign key (dept_name) references department(dept_name)
)

Note: It will be better include dept_id as int in your parent table and create reference it in 2nd table to get better performance.

注意：最好在父表中包含dept_id作为int，并在第二个表中创建引用以获得更好的性能。

create tables as per below-

按以下方式创建表格 -

create table department(
    dept_id int auto_increment,
    dept_name varchar(20),
    building varchar(20) not null,
    budget numeric(8,2),
    primary key (dept_id)
);

create table student (
    ID varchar(5),
    name varchar(20) not null,
    dept_it int,
    tot_cred numeric(3,0),
    primary key (ID),
    foreign key (dept_id) references department(dept_id)
)

#1