CODEWITHSUNDEEP
mysql
user2737955
user2737955

Reputation:

MySQL Foreign Key implementation

I'm new to MySQL and I have two tables named Person & Patient. I'm trying to create a simple foreign key relationship in Patient to a primary key in Person. All examples I've seen online follow the same structure I'm using, but I keep getting errors. Any help is greatly appreciated!

create table PERSON(
    PatientID smallint UNSIGNED NOT NULL,
    Firstname varchar (25),
    Lastname varchar (25),
    CONSTRAINT PatientID_pk PRIMARY KEY (PatientID)
);

And this the table I'm trying to add a foreign key to:

CREATE TABLE PATIENT(
    PatientID smallint UNSIGNED NOT NULL, 
    DoctorID smallint UNSIGNED NOT NULL,
    FOREIGN KEY (PatientID) REFERENCES PERSON(PatientID);

Upvotes: 1

Views: 161

Answers (1)

juergen d
juergen d

Reputation: 204746

I think this is what you wanted

create table PERSON
(
  PersonID smallint UNSIGNED NOT NULL AUTO_INCREMENT,
  Firstname varchar (25),
  Lastname varchar (25),
  CONSTRAINT PersonID_pk PRIMARY KEY (PersonID)
);

CREATE TABLE PATIENT
(
  PatientID smallint UNSIGNED NOT NULL, 
  DoctorID smallint UNSIGNED NOT NULL,
  FOREIGN KEY (PatientID) REFERENCES PERSON(PersonID),
  FOREIGN KEY (DoctorID) REFERENCES PERSON(PersonID),
  UNIQUE KEY unique_key (PatientID, DoctorID)
);

Upvotes: 1

Related Questions