Error code:1215 Cannot add foreign key constraint while running script in MySQL workbench version 6.3

First of all i apologize that the names of the tables and so on are in another language.

The issue is i can't seem to add a foreign key named ID_KOPIE from the table KOPIA_KNIHY into the table SKLAD for some reason. When it gets to adding the foreign key to to the table SKLAD it throws out an error 1215. Here is the code:

CREATE TABLE BOOK (
    BOOK_NAME VARCHAR(30)     NOT NULL,
    YEAR      CHAR(4)         NOT NULL,
    NAME_OF_EDITOR   VARCHAR(30)     NOT NULL,    
    WRITER_ID INTEGER NOT NULL,
    ISBN        VARCHAR(17)     NOT NULL,
    BOOK_ID    INTEGER         NOT NULL,
    PRIMARY KEY (BOOK_ID),
);
CREATE TABLE BOOK_COPY(
    BOOK_ID INTEGER NOT NULL,
    LANGUAGE_CODE CHAR(3) NOT NULL,
    COPY_ID INTEGER NOT NULL,
    BOOK_PICTURES CHAR(1) NOT NULL 
        CHECK (BOOK_PICTURES IN ("Y", "N")),
    PRIMARY KEY (BOOK_ID, LANGUAGE_CODE, COPY_ID)
    FOREIGN KEY(BOOK_ID)
        REFERENCES BOOK(BOOK_ID),
);
CREATE TABLE STORAGE (
    BOOK_ID INTEGER NOT NULL,
    COPY_ID INTEGER NOT NULL,
    BUILDING_ID INTEGER NOT NULL,
    ROOM_NUMBER NUMERIC(4,0) NOT NULL,
    SHELF_NUMBER NUMERIC(4,0) NOT NULL,
    PRIMARY KEY(BOOK_ID, BUILDING_ID, COPY_ID),
    FOREIGN KEY(COPY_ID)
        REFERENCES BOOK_COPY(BOOK_ID),
)

I researched the error code 1215 on the internet, i couldn't find anything wrong with my database. I checked if there's a typo or if i didn't forget to add the reference.

This is the error:

0   769 18:19:37    CREATE TABLE STORAGE (
    BOOK_ID INTEGER NOT NULL,
    COPY_ID INTEGER NOT NULL,
    BUILDING_ID INTEGER NOT NULL,
    ROOM_NUMBER NUMERIC(4,0) NOT NULL,
    SHELF_NUMBER NUMERIC(4,0) NOT NULL,
    PRIMARY KEY(BOOK_ID, BUILDING_ID, COPY_ID),
    FOREIGN KEY(COPY_ID)
        REFERENCES BOOK_COPY(BOOK_ID),
)
Error Code: 1215. Cannot add foreign key constraint 0.016 sec

My question is how can this be fixed that it would work.

Help would be greatly appreciated.

1 answer

  • answered 2018-01-13 17:25 Peter Darmis

    Try this way. Please do alter the ON UPDATE ... ON DELETE syntax in this example with the one you need.

    CREATE TABLE `KNIHA` (
    `NAZOV_KNIHY` VARCHAR(30)     NOT NULL,
    `ROK_PRVEHO_VYDANIA` CHAR(4)         NOT NULL,
    `NAZOV_VYDAVATELA`   VARCHAR(30)     NOT NULL,    
    `ID_AUTORA` INTEGER NOT NULL,
    `ISBN`        VARCHAR(17)     NOT NULL,
    `ID_KNIHY`    INTEGER         NOT NULL,
    PRIMARY KEY (`ID_KNIHY`)
    );
    CREATE TABLE `KOPIA_KNIHY` (
    `ID_KNIHY` INTEGER NOT NULL,
    `KOD_JAZYKA` CHAR(3) NOT NULL,
    `ID_KOPIE` INTEGER NOT NULL,
    `ORAZKY_V_KNIHE` CHAR(1) NOT NULL 
    CHECK (`OBRAZKY_V_KNIHE` IN ("A", "N")),
    INDEX(`ID_KOPIE`),
    PRIMARY KEY (`ID_KNIHY`, `KOD_JAZYKA`, `ID_KOPIE`),
    CONSTRAINT `idx_1` FOREIGN KEY `idx_1` (`ID_KNIHY`) REFERENCES `KNIHA`(`ID_KNIHY`) ON UPDATE CASCADE ON DELETE CASCADE
    );
    
    CREATE TABLE `SKLAD` (
    `ID_KNIHY` INTEGER NOT NULL,
    `ID_KOPIE` INTEGER NOT NULL,
    `ID_BUDOVY` INTEGER NOT NULL,
    `CISLO_MIESTNOSTI` NUMERIC(4,0) NOT NULL,
    `CISLO_REGALU` NUMERIC(4,0) NOT NULL,
    PRIMARY KEY(`ID_KNIHY`, `ID_BUDOVY`, `ID_KOPIE`),
    CONSTRAINT `idx_2` FOREIGN KEY `idx_2` (`ID_KOPIE`) REFERENCES `KOPIA_KNIHY`(`ID_KOPIE`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `idx_3` FOREIGN KEY `idx_3` (`ID_KNIHY`) REFERENCES `KNIHA`(`ID_KNIHY`) ON UPDATE CASCADE ON DELETE CASCADE
    );
    

    Try it on SQL Fiddle

    .