Error while trying to add multiple foreign keys to single table

user_17711607 0
Viewed 29

I am trying to create a child table that constraints 3 foreign keys from the parent but I receive an error 1215: cannot add foreign key constraint

parent table:

    CREATE TABLE `Availability` (
   `time_of_day` varchar(20) NOT NULL,
   `day_of_week` varchar(20) NOT NULL,
   `email` varchar(60) NOT NULL,
   PRIMARY KEY (`time_of_day`,`day_of_week`,`email`),
   KEY `email` (`email`),
   CONSTRAINT `Availability_ibfk_1` FOREIGN KEY (`email`) REFERENCES `service_provider` (`email_service_provider`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

child table (which I cant build due to error mentioned above):

CREATE TABLE TEST1
(
  num_request INT NOT NULL,
  time_of_day VARCHAR(20) NOT NULL,
  day_of_week VARCHAR(20) NOT NULL,
  email VARCHAR(60) NOT NULL,
    PRIMARY KEY (num_request),
    Foreign key (time_of_day) references Availability(time_of_day),
    Foreign key (day_of_week) references Availability(day_of_week),
    Foreign key (email) references Availability(email)
  );

Please show me what I'm doing wrong... Thank you all.

constraints foreign-keys mysql
1 Answers

When you're making a foreign key to a table with a composite primary key (i.e. a key of multiple columns), you should make the foreign key composite as well.

CREATE TABLE TEST1
(
  num_request INT NOT NULL,
  time_of_day VARCHAR(20) NOT NULL,
  day_of_week VARCHAR(20) NOT NULL,
  email VARCHAR(60) NOT NULL,
    PRIMARY KEY (num_request),
    Foreign key (time_of_day, day_of_week, email) references Availability(time_of_day, day_of_week, email)
)

What you tried to do was create three separate constraints.

Bill_Karwin_208600
Related