0

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
michael
  • 29
  • 1
  • 4
  • FK uses not "columns" but "index expression" which is always one. Index expression may consist from more than one columns and/or column prefixes. Of course, the expressions from both sides of foreign key must match fully. – Akina Jun 01 '20 at 08:58
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. – philipxy Jun 01 '20 at 09:19
  • This is a faq. Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. PS When you give a [mre], make it minimal please, this clearly isn't. PS Is this about SQL or your GUI? Please explain--where are you stuck exactly? – philipxy Jun 01 '20 at 09:20
  • I have a problem with SQL. I get an error Error 1215: Cannot add foreign key constraint but I don't know why. I searched for answers on several pages and found nothing. It seems to me that everything is correct but it is not. – michael Jun 01 '20 at 10:23
  • Please clarify via edits, not comments. Please act on my comments. Including making your code minimal--Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) Including googling your error message without your exact strings & with site:SO, etc etc--This is an easily found SO faq & the manual sections on FKs tell you requirements. PS A FK references a PK or UNIQUE with the same columns. You have no such PK/UNIQUE to reference. You don't explain why you wrote that wrong stuff, so we don't know what you should do instead. – philipxy Jun 01 '20 at 11:45
  • Besides your error being a faq, your 1st sentence is a faq. – philipxy Jun 01 '20 at 11:49
  • Googling your error message that is still not in your post with 'before:2014 site:stackoverflow.com' ... https://stackoverflow.com/q/16969060/3404097--question with 334 upvotes & 39 answers with multiple things to check. Googling your 1st sentence with 'site:stackoverflow.com before:2010 sql' ... https://stackoverflow.com/q/953035/3404097--answer with 81 upvotes. Googling although there is still no DBMS version given in your post, 'mysql manual add foreign key 8.0' 1st hit https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html--"Conditions and Restrictions". – philipxy Jun 01 '20 at 14:31

0 Answers0