错误代码1215，无法添加外键约束

I have a quick question here about a database I'm trying to implement. I have referred to several other questions similar to mine, but they don't quite answer my specific question.

我在这里有一个关于我正在尝试实现的数据库的快速问题。我已经提到了其他几个与我类似的问题,但它们并没有完全回答我的具体问题。

I'm relatively new to using SQL, I've taken one class in it a while ago, so I may just need a duh! kind of refresher here.

我对使用SQL比较陌生,我刚刚上了一堂课,所以我可能只需要一个哈!这里有一种复习。

When I try to build this database using MySQL, I'm not able to create any of my tables because of an error saying "Error code 1215, cannot add foreign key constraint"

当我尝试使用MySQL构建这个数据库时,由于错误说“错误代码1215,无法添加外键约束”,我无法创建任何表。

Am I just missing something obvious?

我只是缺少一些明显的东西吗?

Below is my SQL:

下面是我的SQL:

create database emp;
use emp;

select database ();

create table employee
(Fname varchar (15), 
 Minit varchar (1),
 Lname varchar (15), 
 Ssn int (9) not null, 
 Bdate varchar (10), 
 Address varchar (40),
 Sex varchar (1),
 Salary varchar (10), 
 Super_ssn varchar (9),
 Dno int (1), 

 primary key(Ssn),

 foreign key(Super_ssn) references employee(Ssn),
 foreign key(Dno) references department(Dnumber)


)

Engine=INNODB;

create table department
(Dname varchar (15), 
Dnumber int (1) not null, 
Mgr_ssn int (9), 
Mgr_start_date varchar (15),

primary key(Dnumber), 

foreign key(Mgr_ssn) references employee (Ssn) 


)

Engine=INNODB;


create table dept_locations
(Dnumber int (1) not null,
Dlocation varchar (15) not null,

primary key(Dnumber, Dlocation),
foreign key(Dnumber) references department(Dnumber)

)

Engine=INNODB; 

create table project
(Pname varchar (15), 
 Pnumber int (3) not null, 
 Plocation varchar (15), 
 Dnum int (1),

 primary key(Pnumber),
 foreign key(Dnum) references department(Dnumber)

 )

 Engine=INNODB;

 create table works_on
 (Essn int (9) not null, 
  Pno int (3) not null,
  hours float (4),

  primary key(Essn, Pno), 
  foreign key (Essn) references employee(Ssn),
  foreign key(Pno) references project (Pnumber)
  )

  Engine=INNODB;

  create table dependent 

  (Essn int (9) not null, 
   Dependent_name varchar (15) not null, 
   Sex varchar (1), 
   Bdate varchar (10),
   Relationship varchar (10),

   primary key(Essn, Dependent_Name),
   foreign key (Essn) references employee(Ssn)

   )

   Engine=INNODB; 

1 个解决方案

#1

You can't create a foreign key that references another table before you create the other table. So you can't have a foreign key in employee that refers to department before you create the department table. But since the department table has a foreign key that references employee, you can't create them in the opposite order, either.

在创建另一个表之前,无法创建引用另一个表的外键。因此,在创建部门表之前,您不能在引用部门的员工中拥有外键。但由于department表有一个引用employee的外键,因此你也不能以相反的顺序创建它们。

When you create the tables, leave out the foreign keys that refer to tables you haven't created yet, then add them later with ALTER TABLE.

创建表时,请省略引用尚未创建的表的外键,然后使用ALTER TABLE添加它们。

create table employee
(Fname varchar (15), 
 Minit varchar (1),
 Lname varchar (15), 
 Ssn int (9) not null, 
 Bdate varchar (10), 
 Address varchar (40),
 Sex varchar (1),
 Salary varchar (10), 
 Super_ssn varchar (9),
 Dno int (1), 

 primary key(Ssn),
 foreign key(Super_ssn) references employee(Ssn),
);

create table department
(Dname varchar (15), 
Dnumber int (1) not null, 
Mgr_ssn int (9), 
Mgr_start_date varchar (15),

primary key(Dnumber),
foreign key(Mgr_ssn) references employee (Ssn) 
);

alter table employee add constraint foreign key (Dno) references department(Dnumber);