foriegn key error on table generation

[demuro1]

Hello, I'm building this database for an assignment at school. I'm executing the following code in blocks. I have executed blocks 1,2,3, and 4. Block 5 gives me this error though:

Msg 1767, Level 16, State 0, Line 1Foreign key 'FK_apptId' references invalid table 'appointment'.Msg 1750, Level 16, State 0, Line 1Could not create constraint. See previous errors.

I am using MSSQL Server 2008 R2 Any suggestions? Also if you have any recomendations I'd appreciate it. Thanks for your help block 1

CREATE DATABASE drOfficeDB;GO

block 2

USE drOfficeDB;GO

block 3

CREATE TABLE patient(patientId char(10)  not null, --patient id (primary key)fname  nvarchar(50) not null, --first namelname  nvarchar(50) not null, --last nameminitial nchar(1)  null,  --middle initialemail  nvarchar(150) null,  --email addressphone  char(10)  not null, --phone numberaddress  nvarchar(150) not null, --street addresscity  nvarchar(50) not null, --citystate  nvarchar(20) not null, --statezipcode  varchar(10)  not null, --zip codeCONSTRAINT PK_patientId PRIMARY KEY CLUSTERED  (   patientId  ));GO

block 4

create table insurance(insuranceId char(10) not null, --insurance id (primary key)patientId char(10) not null, --patient id (foreign key)grpPolId char(20) not null, --group policy idpolicyId char(20) not null, --policy idcopay  money  not null --copay amountCONSTRAINT PK_insuranceId PRIMARY KEY CLUSTERED(  insuranceId)CONSTRAINT FK_patientId FOREIGN KEY(patientId)REFERENCES patient  (patientId));GO

block 5

CREATE TABLE appointment(apptId  char(10)  not null, --appointment id (primary key)patientId char(10)  not null, --patient id (foreign key)starttime time   not null, --appointment start timeapptdate date   not null, --appointment dateproblem  nvarchar(max) not null, --patient problem descriptionweight  decimal(5,2) not null, --patient weightsystolicBp int	not null, --systolic blood pressurediastolicBp int	not null --diastolic blood pressureCONSTRAINT PK_apptId PRIMARY KEY CLUSTERED(  apptId)constraint FK_patientId foreign key(patientId)references patient (patientId));go

block 6

CREATE TABLE diagnosis(diagnosisId char(10)  not null, --diagnosis id (primary key)apptId  char(10)  not null, --appointment id (foriegn key)diganosis nvarchar(max) not null, --diagnosisdiagNotes nvarchar(max) not null, --diagnosis notesrequiresRef bit	not null --requires referal (yes/no)CONSTRAINT PK_diagnosisId PRIMARY KEY CLUSTERED(  diagnosisId)CONSTRAINT FK_apptId FOREIGN KEY(apptId)REFERENCES appointment  (apptId));GO

block 7

create table referral(referralid char(10)  not null, --referral id (primary key)apptId  char(10)  not null, --appointment id (foreign key)referralDr nvarchar(150) not null, --referral doctoremail  nvarchar(150) null,  --email addressphone  char(10)  not null, --phone numberaddress  nvarchar(150) not null, --street addresscity  nvarchar(50) not null, --citystate  nvarchar(20) not null, --statezipcode  varchar(10)  not null, --zip codeCONSTRAINT PK_referralId PRIMARY KEY CLUSTERED(  referralId)CONSTRAINT FK_apptId FOREIGN KEY(apptId)REFERENCES appointment  (apptId));go

block 8

create table insurancecreate table perscription(perscriptionId char(10)  not null, --perscription id (primary key)apptId   char(10)  not null, --appointment id (foriegn key)perscription nvarchar(200) not null, --perscription namequantity  int	not null, --medicine quantity per perscriptioninstructions nvarchar(max) not null, --dosing instructionsnumOfRenewal int	not null, --number of renewalslastRenewed  date   not null --date perscription was last renewedCONSTRAINT PK_perscriptionId PRIMARY KEY CLUSTERED(  perscriptionId)CONSTRAINT FK_apptId FOREIGN KEY(apptId)REFERENCES appointment  (apptId));go 

[justsomeguy]

That error message would come from block 6, it sounds like you didn't run block 5. Look at the error: Foreign key 'FK_apptId' references invalid table 'appointment'. Block 6 creates FK_apptId, and if it says there is no appointment table then it sounds like you didn't run the code to create it.

[demuro1]

you're right I got almost an identical error message running block 5 though. something about the foreign key not executing because the table was invalid. I'll get the exact error message

[demuro1]

ok Finally had time to get back to this assignment. and ge tthe error codes. so first the code then the errors any help would be greatly appreciated thanks

CREATE DATABASE drOfficeDB;GOUSE drOfficeDB;GOCREATE TABLE patient(patientId char(10)  not null, --patient id (primary key)fname  nvarchar(50) not null, --first namelname  nvarchar(50) not null, --last nameminitial nchar(1)  null,  --middle initialemail  nvarchar(150) null,  --email addressphone  char(10)  not null, --phone numberaddress  nvarchar(150) not null, --street addresscity  nvarchar(50) not null, --citystate  nvarchar(20) not null, --statezipcode  varchar(10)  not null, --zip codeCONSTRAINT PK_patientId PRIMARY KEY CLUSTERED  (   patientId  ));GOcreate table insurance(insuranceId char(10) not null, --insurance id (primary key)patientId char(10) not null, --patient id (foreign key)grpPolId char(20) not null, --group policy idpolicyId char(20) not null, --policy idcopay  money  not null --copay amountCONSTRAINT PK_insuranceId PRIMARY KEY CLUSTERED(  insuranceId)CONSTRAINT FK_patientId FOREIGN KEY(patientId)REFERENCES patient  (patientId));GOCREATE TABLE appointment(apptId  char(10)  not null, --appointment id (primary key)patientId char(10)  not null, --patient id (foreign key)starttime time   not null, --appointment start timeapptdate date   not null, --appointment dateproblem  nvarchar(max) not null, --patient problem descriptionweight  decimal(5,2) not null, --patient weightsystolicBp int    not null, --systolic blood pressurediastolicBp int    not null --diastolic blood pressureCONSTRAINT PK_apptId PRIMARY KEY CLUSTERED(  apptId)constraint FK_patientId foreign key(patientId)references patient (patientId));goCREATE TABLE diagnosis(diagnosisId char(10)  not null, --diagnosis id (primary key)apptId  char(10)  not null, --appointment id (foriegn key)diganosis nvarchar(max) not null, --diagnosisdiagNotes nvarchar(max) not null, --diagnosis notesrequiresRef bit    not null --requires referal (yes/no)CONSTRAINT PK_diagnosisId PRIMARY KEY CLUSTERED(  diagnosisId)CONSTRAINT FK_apptId FOREIGN KEY(apptId)REFERENCES appointment  (apptId));GOcreate table referral(referralid char(10)  not null, --referral id (primary key)apptId  char(10)  not null, --appointment id (foreign key)referralDr nvarchar(150) not null, --referral doctoremail  nvarchar(150) null,  --email addressphone  char(10)  not null, --phone numberaddress  nvarchar(150) not null, --street addresscity  nvarchar(50) not null, --citystate  nvarchar(20) not null, --statezipcode  varchar(10)  not null, --zip codeCONSTRAINT PK_referralId PRIMARY KEY CLUSTERED(  referralId)CONSTRAINT FK_apptId FOREIGN KEY(apptId)REFERENCES appointment  (apptId));gocreate table insurancecreate table perscription(perscriptionId char(10)  not null, --perscription id (primary key)apptId   char(10)  not null, --appointment id (foriegn key)perscription nvarchar(200) not null, --perscription namequantity  int    not null, --medicine quantity per perscriptioninstructions nvarchar(max) not null, --dosing instructionsnumOfRenewal int    not null, --number of renewalslastRenewed  date   not null --date perscription was last renewedCONSTRAINT PK_perscriptionId PRIMARY KEY CLUSTERED(  perscriptionId)CONSTRAINT FK_apptId FOREIGN KEY(apptId)REFERENCES appointment  (apptId));go

errors

Msg 2714, Level 16, State 4, Line 2There is already an object named 'FK_patientId' in the database.Msg 1750, Level 16, State 0, Line 2Could not create constraint. See previous errors.Msg 1767, Level 16, State 0, Line 2Foreign key 'FK_apptId' references invalid table 'appointment'.Msg 1750, Level 16, State 0, Line 2Could not create constraint. See previous errors.Msg 156, Level 15, State 1, Line 16Incorrect syntax near the keyword 'CONSTRAINT'.Msg 156, Level 15, State 1, Line 4Incorrect syntax near the keyword 'create'.

[justsomeguy]

There is already an object named 'FK_patientId' in the database.

That one should be self-explanatory. It doesn't create the appointment table because it has a constraint with the same name as one that is already there. This error:

Msg 156, Level 15, State 1, Line 16Incorrect syntax near the keyword 'CONSTRAINT'.

Is because of an extra comma before that line. This error:

Msg 156, Level 15, State 1, Line 4Incorrect syntax near the keyword 'create'.

is because of these lines: create table insurancecreate table perscription(

[demuro1]

I'm sorry for the late reply on this but your help solved my problem. Thanks so much for your help!!!!