SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Oct 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you've overlooked giving your foreign keys a datatype

    for example,
    Code:
    CREATE TABLE Doctors_Appointment
     (Doc_Appointment_Number INTEGER 
         REFERENCES ... )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937 for your quick response. When though and how can I show the InnoDB STATUS in the code?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    wha?

    i'm sorry, i don't understand the question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Oct 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    obviously, you got that error message on a CREATE TABLE statement

    please show that statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Oct 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much r937.com | rudy.ca , i managed to fix the problem. It was on the foreign keys. I appreciate your help.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •