It is possible to create a foreign key with 2 columns that will connect with 2 other columns?
I have a table with workers. Each worker is assigned to a single shift and a bus stop - it's an application for carriers.
I need to keep time when the worker drives to work, and when he returns.
CREATE TABLE IF NOT EXISTS `bus_stop` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `worker_shift` (
`id` INT(10) UNSIGNED NOT NULL,
`name` VARCHAR(40) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS `bus_stop_time` (
`id` INT(10) UNSIGNED NOT NULL,
`shiftId` INT(11) UNSIGNED NOT NULL,
`busStopId` INT(11) UNSIGNED NOT NULL,
`time` TIME NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_bus_stop_time_shiftId_worker_shift_id` (`shiftId` ASC),
INDEX `idx_bus_stop_time_busStopId_bus_stop_id` (`busStopId` ASC),
INDEX `idx_bus_stop_time_busStopId_shiftId_bus_stop_busStopId_shiftId` (`busStopId` ASC, `shiftId` ASC),
CONSTRAINT `fk_bus_stop_time_busStopId_bus_stop_id`
FOREIGN KEY (`busStopId`)
REFERENCES `bus_stop` (`id`),
CONSTRAINT `fk_bus_stop_time_shiftId_worker_shift_id`
FOREIGN KEY (`shiftId`)
REFERENCES `worker_shift` (`id`))
ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS `worker_planner` (
`id` INT(10) UNSIGNED NOT NULL,
`shiftId` INT(11) UNSIGNED NOT NULL,
`busStopId` INT(11) UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_worker_planner_shiftId_worker_shift_id` (`shiftId` ASC),
INDEX `idx_worker_planner_busStopId_bus_stop_id` (`busStopId` ASC),
CONSTRAINT `fk_worker_planner_busStopId_bus_stop_id`
FOREIGN KEY (`busStopId`)
REFERENCES `bus_stop` (`id`),
CONSTRAINT `fk_worker_planner_shiftId_worker_shift_id`
FOREIGN KEY (`shiftId`)
REFERENCES `worker_shift` (`id`))
ENGINE = InnoDB
SQL Statement:
ALTER TABLE `bus_stop_time`
ADD CONSTRAINT `fk_wp`
FOREIGN KEY (`busStopId` , `shiftId`)
REFERENCES `worker_planner` (`busStopId` , `shiftId`)
ON DELETE CASCADE
ON UPDATE CASCADE