I am trying to work out how to configure a composite foreign key.
Some background information:
I am developing a system for an event run by volunteers. The system assigns Volunteers to work in certain Areas during certain Sessions.
So I have Volunteer, Area, and Session domain objects.
When a volunteer signs up, they specify what times (Sessions) they want to work, and what jobs (Areas) they are willing to do.
I have already set up a many-to-many relationship between Volunteer and Session and another between Volunteer and Area.
I have another object, Assignment. This states the Area to which a Volunteer is assigned for a specific Session.
The Assignment table already has a composite-id (Volunteer and Session) and another column for Area, but I need to add foreign key constraints such that:
* Volunteer and Session together must appear in the Volunteer_Session table (join table between Volunteer and Session) to ensure that only valid Volunteer/Session combinations have an assignment;
* Volunteer and Area together must appear in Volunteer_Area table (join between Volunteer and Area) to make sure that volunteers are only assigned to do jobs they are willing to do.

In MySQL, here's the table structure I'm trying to achieve:

CREATE TABLE `assignment` (
`volunteer_id` int(11) NOT NULL DEFAULT '0',
`session_id` int(11) NOT NULL DEFAULT '0',
`area_id` int(11) DEFAULT NULL,

(some other columns...)

PRIMARY KEY (`volunteer_id`,`session_id`),
KEY `assignment_volunteer_area` (`volunteer_id`,`area_id`),
KEY `assignment_volunteer_session` (`volunteer_id`,`session_id`),
KEY `assignment_area_session` (`area_id`,`session_id`),
CONSTRAINT `assignment_area_session` FOREIGN KEY (`area_id`, `session_id`) REFERENCES `area_session` (`area_id`, `session_id`) ON DELETE CASCADE,
CONSTRAINT `assignment_volunteer_area` FOREIGN KEY (`volunteer_id`, `area_id`) REFERENCES `volunteer_area` (`volunteer_id`, `area_id`) ON DELETE CASCADE,
CONSTRAINT `assignment_volunteer_session` FOREIGN KEY (`volunteer_id`, `session_id`) REFERENCES `volunteer_session` (`volunteer_id`, `session_id`)