MySQL Foreign Key Issues

Hi guys, I am a newbie with mysql. I am creating tables but i get stuck when it comes to foreign keys. I tried to remove the foreign keys and than paste the rest of the code, add an additional column of the foreign key but still its not working. Could someone assist me please. i will really be grateful.

create table Doctors_Appointment

(Doc_Appointment_Number REFERENCES Patient_Appointment_Number(Doc_Appointment_Number) not null,
Treatment_ID REFERENCES Doctors_Treatment(Treatment_ID)not null))

create table Employee_Appointment

(Employee_ID REFERENCES Employee(Employee_ID)not null,
Doc_Appointment_Number REFERENCES Patient_Appointment(Doc_Appointment_Number)not null))

create table Hospital_Invoice

(Invoice_Number varchar(16)PRIMARY KEY,
Doc_Appointment_Number REFERENCES Patient_Appointment(Doc_Appointment_Number)not null,
Invoice_Date date not null,
Total_Cost decimal(15,2) not null,
Payment_Date date)

create table Payment_Type

(Invoice_Number REFERENCES Hospital_Invoice(Invoice_Number) not null,
Payment_ID REFERENCES Payment(Payment_ID) not null)

you’ve overlooked giving your foreign keys a datatype

for example,


CREATE TABLE Doctors_Appointment
 (Doc_Appointment_Number [COLOR="#FF0000"]INTEGER [/COLOR]
     REFERENCES ... )

Thanks r937 for your quick response. When though and how can I show the InnoDB STATUS in the code?

wha?

i’m sorry, i don’t understand the question

@r937.com | rudy.ca , InnoDB is the storage engine for MySQL. Am i supposed to include it within my code? On the other hand, after making the proposed changes, I still get the following error after : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘not null, Treatment_ID REFERENCES Doctors_Treatment(Treatment_ID)not null))’ at line 3.

obviously, you got that error message on a CREATE TABLE statement

please show that statement

Thanks so much r937.com | rudy.ca , i managed to fix the problem. It was on the foreign keys. I appreciate your help.