Cannot add foreign key constraint - MySQL workbench

Question

I am aware that there are several other posts on this problem, but they normally have an apparent cause. I don't see what the problem is with my code.

I have tried:

• making sure ENGINE = INNODB.
• making sure the same datatype is used.
• removing code and only testing the tables where the error happens.

Below is the code. There are two tables, DESSERT and OFFER. I am creating two foreign keys in OFFER that correspond to the primary keys in DESSERT.

Important fact:

• I can comment away the second foreign key (corresponding to DESSERT_DATE), and the code will run. I have no problems creating the first foreign key.

Thanks for any help.

CREATE TABLE DESSERT (
DESSERT_CODE VARCHAR(10) NOT NULL,
DESSERT_DATE VARCHAR(40) NOT NULL,
DESSERT_DESCR VARCHAR(160),
DESSERT_NAME VARCHAR(10),
DRINK_NAME VARCHAR(26),
TOPPING_NAME VARCHAR(26),
DESSERT_PRICE_AMOUNT NUMERIC(6, 2),

PRIMARY KEY(DESSERT_DATE, DESSERT_CODE)
);


CREATE TABLE OFFER (
DESSERT_CODE VARCHAR(10) NOT NULL,
DESSERT_DATE VARCHAR(40) NOT NULL,
RESTAURANT_ID VARCHAR(30) NOT NULL,

PRIMARY KEY(DESSERT_CODE, DESSERT_DATE, RESTAURANT_ID),
FOREIGN KEY (DESSERT_CODE) REFERENCES DESSERT(DESSERT_CODE),
FOREIGN KEY (DESSERT_DATE) REFERENCES DESSERT(DESSERT_DATE)
);

EDIT: So making the foreign key composite and changing the order did the trick.

Answer 1

If you want separated foreign key, you need add unique key on DESSERT_CODE in DESSERT table.(But it may have totally different meaning)

This works:

CREATE TABLE DESSERT (
DESSERT_CODE VARCHAR(10) NOT NULL,
DESSERT_DATE VARCHAR(40) NOT NULL,
DESSERT_DESCR VARCHAR(160),
DESSERT_NAME VARCHAR(10),
DRINK_NAME VARCHAR(26),
TOPPING_NAME VARCHAR(26),
DESSERT_PRICE_AMOUNT NUMERIC(6 , 2 ),
PRIMARY KEY (DESSERT_DATE , DESSERT_CODE),
UNIQUE KEY (DESSERT_CODE)
);


CREATE TABLE OFFER (
DESSERT_CODE VARCHAR(10) NOT NULL,
DESSERT_DATE VARCHAR(40) NOT NULL,
RESTAURANT_ID VARCHAR(30) NOT NULL,
PRIMARY KEY (DESSERT_CODE , DESSERT_DATE , RESTAURANT_ID),
FOREIGN KEY (DESSERT_CODE)
    REFERENCES DESSERT (DESSERT_CODE),
FOREIGN KEY (DESSERT_DATE)
    REFERENCES DESSERT (DESSERT_DATE)
);

Or you need make sure you use both columns in your foreign key, as Misandrist already answered.

CREATE TABLE DESSERT (
DESSERT_CODE VARCHAR(10) NOT NULL,
DESSERT_DATE VARCHAR(40) NOT NULL,
DESSERT_DESCR VARCHAR(160),
DESSERT_NAME VARCHAR(10),
DRINK_NAME VARCHAR(26),
TOPPING_NAME VARCHAR(26),
DESSERT_PRICE_AMOUNT NUMERIC(6 , 2 ),
PRIMARY KEY (DESSERT_DATE , DESSERT_CODE)
);


CREATE TABLE OFFER (
DESSERT_CODE VARCHAR(10) NOT NULL,
DESSERT_DATE VARCHAR(40) NOT NULL,
RESTAURANT_ID VARCHAR(30) NOT NULL,
PRIMARY KEY (DESSERT_CODE , DESSERT_DATE , RESTAURANT_ID),
FOREIGN KEY (DESSERT_DATE , DESSERT_CODE)
    REFERENCES DESSERT (DESSERT_DATE , DESSERT_CODE)
);

Answer 2

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

CREATE TABLE IF NOT EXISTS `mydb`.`DESSERT` (
  `DESSERT_CODE` INT(11) NOT NULL,
  `DESSERT_DATE` VARCHAR(45) NOT NULL,
  `DESSERT_DESCR` VARCHAR(45) NULL DEFAULT NULL,
  `DESSERT_NAME` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`DESSERT_CODE`, `DESSERT_DATE`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE TABLE IF NOT EXISTS `mydb`.`OFFER` (
  `id` INT(11) NOT NULL,
  `DESSERT_DESSERT_CODE` INT(11) NOT NULL,
  `DESSERT_DESSERT_DATE` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_OFFER_DESSERT_idx` (`DESSERT_DESSERT_CODE` ASC, `DESSERT_DESSERT_DATE` ASC),
  CONSTRAINT `fk_OFFER_DESSERT`
    FOREIGN KEY (`DESSERT_DESSERT_CODE` , `DESSERT_DESSERT_DATE`)
    REFERENCES `mydb`.`DESSERT` (`DESSERT_CODE` , `DESSERT_DATE`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

mydb should be replace to your database name

Answer 3

I think what you really need is:

CREATE TABLE OFFER (
    DESSERT_CODE VARCHAR(10) NOT NULL,
    DESSERT_DATE VARCHAR(40) NOT NULL,
    RESTAURANT_ID VARCHAR(30) NOT NULL,

    PRIMARY KEY(DESSERT_DATE, DESSERT_CODE, RESTAURANT_ID),
    FOREIGN KEY (DESSERT_DATE, DESSERT_CODE) REFERENCES DESSERT(DESSERT_DATE, DESSERT_CODE)
);

Since the primary key is composite, all foreign keys referencing it must also be composite.