0

I have three tables patient, disease and provider.all three tables are relatable by patient_id and disease_name. I know that you will need: CONSTRAINT, FOREIGN KEY, and REFERENCES in order to complete the relations of all three tables. I am getting an error on line 7 can someone tell me what I'm doing wrong? I cant post a picture of the problem, but here is the error: Error SQL query:

MySQL said: Documentation

#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 ''patient_id')

CREATE TABLE patient(
    patient_id INT (3) NOT NULL AUTO_INCREMENT,
    place VARCHAR (20) NOT NULL,
    profession VARCHAR (30)NOT NULL,
    age INT (11),
    PRIMARY KEY (patient_id),
    CONSTRAINT patient_info FOREIGN KEY (patient_id) REFERENCES patient ('patient_id')
);
CREATE TABLE disease (
    patient_id INT (3) NOT NULL AUTO_INCREMENT,
    disease_name VARCHAR (20) NOT NULL,
    duration_of_stay INT(11),
    CONSTRAINT patient FOREIGN KEY(patient_id) REFERENCES patient ('patient_id'),
    CONSTRAINT disease_info FOREIGN KEY(disease_name) REFERENCES disease ('disease_name') 
);

CREATE TABLE provider(
    disease_id INT (3) NOT NULL AUTO_INCREMENT,
    disease_name VARCHAR (20) NOT NULL,
    doctor_name VARCHAR (20) NOT NULL,
    CONSTRAINT disease_info FOREIGN KEY (disease_name) REFERENCES disease ('disease_name')
);
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Dandrew
  • 1
  • 1
  • its not the same question – Dandrew Jan 26 '18 at 22:06
  • Same answer though. – Uueerdo Jan 26 '18 at 23:49
  • The table design really needs revisited. `patient` seems ok, other than referencing itself. The `disease` probably should not be directly related to `patient`, as multiple patients could have the same disease. Similarly, a `provider` could "provide" for multiple `patient`s with varying `disease`s, so should not reference either directly. A `stay` table (with patient_id, disease_id, provider_id, and duration of stay) would bring the three tables together. – Uueerdo Jan 26 '18 at 23:55

0 Answers0