Using Multiple Constraints on Foreign Key.

[techdude]

Hi. I have two tables:

 

instructor_dates: instructor_id, calendar_date

client_dates: instructor_id, client_id, calendar_date.

 

I want to add a foreign key constraint that checks for the following:

 

When inserting into client_dates, does calendar_date exists where the instructor_id = <whatever>;

 

I would like this to be implemented as a constraint as I can write sql to check this prior to insertion, but I want the rule to apply to the other people who are working on this project. Not keeping the two tables in sync (for example client_date exists where an instructor date does not) could cause problems and I wan't to be able to ensure that is the case.

 

In short, I am asking how to create a foreign key constraint on multiple fields.

 

Thanks!

--

techdude

 

[techdude]

What is it with me always answering my own questions, only minutes after I ask them? I sit there pondering a question and researching it for hours (Well, ok this time it was only 1.25 hours) until I decide to post it to this forum, only to have a spark of inspiration 5 minutes later, and solve my own question.

 

Oh, well.

 

Here is the solution. Add a foreign key constraint like this:

CONSTRAINT `client_dates_ibfk_5` FOREIGN KEY (`instructor_id`, `calendar_date`) REFERENCES `instructor_dates` (`instructor_id`, `calendar_date`) ON DELETE CASCADE ON UPDATE CASCADE

 

'til next time,

 

--

techdude