I want to create those tables:

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),
);

But when I want to create the Account table, I get this error:

Msg 1767, Level 16, State 0, Line 71
Foreign key 'FK__Account__Instruc__6CA31EA0' references invalid table 'PersonalInstructor'.

Msg 1750, Level 16, State 1, Line 71
Could not create constraint or index. See previous errors.

And when I want to create the PersonalInstructor table, I get this error:

Msg 1767, Level 16, State 0, Line 96
Foreign key 'FK__PersonalI__Accou__6F7F8B4B' references invalid table 'Account'.

Msg 1750, Level 16, State 1, Line 96
Could not create constraint or index. See previous errors.

I know it is due to the foreign key because the tables are not created. Can I create the tables and after to add the foreign keys later, or how I should do this?

CodePudding user response：

Yes you can create your tables then add the constraints :)

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