使用外键在SQL中创建表

Question

我想创建这些表:

CREATE DATABASE [HEALTH CLUB Database]
Go
USE [HEALTH CLUB Database]
GO
CREATE TABLE Member
(
MemberID Integer NOT NULL IDENTITY(0,1),
AccountID Integer NULL,
FirstName Varchar(40) NOT NULL,
LastName Varchar(40) NOT NULL,
DateOfBirth Date NOT NULL,
MemberAddress Varchar(256) NOT NULL,
MontlyFee Float NOT NULL,
PhoneNumber Varchar(20) NULL,
Passwd varchar(40) NULL,
UserName varchar(40) NULL,
CONSTRAINT MemberPK 
PRIMARY KEY(MemberID),
CONSTRAINT ValidBirthYear 
CHECK((DATEDIFF(year, DateOfBirth, GETDATE()) > 18) 
OR (DATEDIFF(year, DateOfBirth, GETDATE()) = 18 
AND DATEDIFF(month, DateOfBirth, GETDATE()) > 0) 
OR (DATEDIFF(year, DateOfBirth, GETDATE()) = 18 
AND DATEDIFF(month, DateOfBirth, GETDATE()) = 0) 
AND DATEDIFF(day, DateOfBirth, GETDATE()) <= 0),
CONSTRAINT AccountFK 
FOREIGN Key(AccountID) REFERENCES Account(AccountID)
);
CREATE TABLE Instructor
(
InstructorID Integer NOT NULL IDENTITY(0,1),
FirstName Varchar(30) NOT NULL,
LastName Varchar(40) NOT NULL,
DateOfBirth DATE NOT NULL,
InstructorAddress Varchar(256) NOT NULL,
PhoneNumber Varchar(40) NULL,
JobTitle Varchar(40) NOT NULL,
PayGrade Float NOT NULL,
ChargingFee Float NULL,
Passwd varchar(40) NOT NULL,
UserName varchar(40) NOT NULL,
MaximumAvailablePerWeek Integer NOT NULL,
CONSTRAINT InstructorPK 
PRIMARY KEY(InstructorID),
CONSTRAINT ValidBirthYearInstructor 
CHECK( (DATEDIFF(year, DateOfBirth, getdate())>18) 
or (DATEDIFF(year, DateOfBirth, getdate())=18 and DATEDIFF(month, DateOfBirth, getdate())>0) 
or (DATEDIFF(year, DateOfBirth, getdate())=18 AND DATEDIFF(month, DateOfBirth, getdate())=0) AND DATEDIFF(day, DateOfBirth, getdate())=0)
);
CREATE TABLE Class 
(
ClassID Integer NOT NULL IDENTITY(0,1),
ClassName VARCHAR(256) NOT NULL,
PRICE FLOAT NOT NULL,
[DESCRIPTION] VARCHAR(1000) NULL 
DEFAULT 'This class does not have a description right now',
Dates DateTime NOT NULL,
MaximumAttendance Integer NOT NULL,

CONSTRAINT ClassPK 
PRIMARY KEY (ClassID, Dates),
);
CREATE TABLE Account 
(
AccountID Integer NOT NULL IDENTITY(0,1),
MemberID Integer Not NULL,
MontlyFee Double Precision NULL,
InstructorID Integer NULL 
REFERENCES PersonalInstructor(InstructorID),
CONSTRAINT AccountPK 
PRIMARY KEY (AccountID),
);
CREATE TABLE AccoutnAttandance
(
AccountID Integer NOT NULL 
REFERENCES Account(AccountID),
[Date] DateTime NOT NULL,
Class Varchar(50) NOT NULL,
StateofBooking varchar(50) NULL
DEFAULT 'Incomplete',

CONSTRAINT AttandancePK 
PRIMARY KEY (Class, [Date]),
);
CREATE TABLE PersonalInstructor
(
Class Varchar(50) NOT NULL,
AccountID Integer NOT NULL 
REFERENCES Account(AccountID),
InstructorID Integer NOT NULL 
REFERENCES Instructor(InstructorID),
CONSTRAINT PersonalInstructorPK 
PRIMARY KEY (Class),
);

但是当我想创建Account表时，我得到这个错误:

Msg 1767, Level 16, State 0, Line 71
外键'FK__Account__Instruc__6CA31EA0'引用了无效表'PersonalInstructor'。

Msg 1750, Level 16, State 1, Line 71
无法创建约束或索引。见前面的错误。

当我想创建PersonalInstructor表时，我得到这个错误:

Msg 1767, Level 16, State 0, Line 96
外键'FK__PersonalI__Accou__6F7F8B4B'引用了无效表'Account'。

Msg 1750, 16层，状态1,96行
无法创建约束或索引。见前面的错误。

我知道这是由于外键，因为表没有创建。我可以先创建表，然后再添加外键吗?或者我应该怎么做?

Answer 1

是的，你可以创建你的表，然后添加约束:)

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);