创建一个数据库

Question

CREATE TABLE EMPLOYEE 
(Fname VARCHAR(15) NOT NULL, 
Minit CHAR, 
Lname VARCHAR(15) NOT NULL, 
Ssn CHAR(9) NOT NULL, 
Bdate DATE, 
Address VARCHAR(30), 
Sex CHAR, 
Salary DECIMAL(10,2), 
Super_ssn CHAR(9), 
Dno INT NOT NULL, 
PRIMARY KEY (Ssn), 
FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn), 
FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)); 

CREATE TABLE DEPARTMENT 
(Dname VARCHAR(15) NOT NULL, 
Dnumber INT NOT NULL, 
Mgr_ssn CHAR(9) NOT NULL, 
Mgr_start_date DATE, 
PRIMARY KEY (Dnumber), 
UNIQUE (Dname), 
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)); 

CREATE TABLE DEPT_LOCATIONS 
(Dnumber INT NOT NULL, 
Dlocation VARCHAR(15) NOT NULL, 
PRIMARY KEY (Dnumber, Dlocation), 
FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber)); 

CREATE TABLE PROJECT 
(Pname VARCHAR(15) NOT NULL, 
Pnumber INT NOT NULL, 
Plocation VARCHAR(15), 
Dnum INT NOT NULL, 
PRIMARY KEY (Pnumber), 
UNIQUE (Pname), 
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber)); 

CREATE TABLE WORKS_ON 
(Essn CHAR(9) NOT NULL, 
Pno INT NOT NULL, 
Hours DECIMAL(3,1) NOT NULL, 
PRIMARY KEY (Essn, Pno), 
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn), 
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber)); 

CREATE TABLE DEPENDENT 
(Essn CHAR(9) NOT NULL, 
Dependent_name VARCHAR(15) NOT NULL, 
Sex CHAR, 
Bdate DATE, 
Relationship VARCHAR(8), 
PRIMARY KEY (Essn, Dependent_name), 
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)); 

这里我不能够创建任何表，因为每个表使用外键constraint.So好心帮我如何创建整个database.for如引用到其他表。如果我正在创建EMPLOYEE表，它正在搜索DEPARTMENT表并且不允许创建该表，并且正在尝试创建DEPARTMENT，然后它再次搜索EMPLOYEE表时不允许创建该表。

Answer 1

您可以尝试创建并填充没有外键约束的表，然后添加外键约束。

Answer 2

只需在创建表时添加外键，例如，

ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMPLOYEE_DEPARTMENT 
    FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber); 

Answer 3

一个简单的解决方法是从创建EMPLOYEE表中删除FK到DEPARTMENT（下面的一个），并创建所有表/模式。

FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber) 

此后，重新创建FK上EMPLOYEE表说

ALTER TABLE EMPLOYEE ADD CONSTRAINT fk_emp 
FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber); 

看到一个成功创建表演示http://sqlfiddle.com/#!2/410ce