Error while trying to add multiple foreign keys to single table-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

Though now I have a new issue, trying to add a row to the child table above, but I receive an error 1452: cannot add or update a child row.

This is the row I have in the Parent Table:

time_of_day | day_of_week | email

'Evening' 'monday' '[email protected]'

but while trying to add a row to TEST1 (the child table) I receive the error 1452 - cannot add or update a child row:

insert into TEST1 (num_request, time_of_day, day_of_week, email)
Values (1, 'Evening', 'monday', '[email protected]')

I don't see what I'm missing, I do have these rows in the parent table, the type of input is the same...