时间戳

首页 > 解决方案 > mysql“无法添加外键约束”

mysql - mysql“无法添加外键约束”

问题描述

我正在尝试运行以下 SQL（在 MySQL 服务器上）创建语句：

Create table if not exists Employees (id integer primary key, name char(40) not null, department char(40) not null, salary int not null, phone char(40) not null)
Create table if not exists Managers (mid integer primary key, name char(40) not null, department char(40) not null, salary integer not null, phone char(40) not null)
Create table if not exists Departments (did integer primary key, dname char(40) not null, numberofworkers integer not null, manager char(40) not null)
Create table if not exists works_in (id integer, did integer, primary key (id, did), foreign key Employees(id) references Employees, foreign key Departments(did) references Departments)
Create table if not exists Management (mid integer, did integer, primary key (mid, did), foreign key Managers(mid) references Managers, foreign key Departments(did) references Departments)

除了最后两行，一切都很好，我得到：

ERROR 1215 (HY000): Cannot add foreign key constraint

我试着跑去SHOW ENGINE INNODB STATUS;看看问题出在哪里，它给出了：

Error in foreign key constraint of table project/works_in:
foreign key Employees(id) references Employees, foreign key Departments(did) references Departments):
Syntax error close to:
, foreign key Departments(did) references Departments)

但这无论如何都没有帮助。我尝试检查其他类似的问题，但其中大多数建议列应该是相同的类型，这里已经是这种情况。

为什么我会收到此错误？

标签： mysqlsql

解决方案

声明的语法foreign key必须包括在“其他”表中引用的键：

foreign key (did) references Departments (did)
foreign key (mid) references Managers(mid)
foreign key Departments (did) references Departments (did)

创建表格时，我强烈建议您将每一列放在单独的行上：

Create table if not exists works_in (
    id integer,
    did integer,
    primary key (id, did),
    foreign key (id) references Employees(id),
    foreign key (did) references Departments(did)
);

你怎么能读到一长串不间断的列定义？

推荐阅读