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

Question

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.

Are you sure you know what you are doing? (Usually) You should only reference a UNIQUE (or PRIMARY) KEY. At least it should be logically unique. — Paul Spiegel, Jan 13 '18 at 17:55
Well i am just starting with SQL and databases so there's a big chance i don't know what i am doing. So with that in mind, is there a way to rework this so that i don't have to use the indexes? — S. Kopecký, Jan 13 '18 at 18:04
As @PaulSpiegel pointed `Additionally, MySQL requires that the referenced columns be indexed for performance reasons. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys.` Taken from MySQL documentation. So if `ID_KOPIE` is `UNIQUE` then change it accordingly. — , Jan 13 '18 at 18:12
@S.Kopecký it's not just about adding indexes. You can "fix" the error message away by reordering the primary key of `KOPIA_KNIHY` to `(ID_KOPIE, ID_KNIHY, KOD_JAZYKA)`. But first you need to undestand the relations of your tables. — Paul Spiegel, Jan 13 '18 at 18:16
Related: https://stackoverflow.com/questions/588741/can-a-foreign-key-reference-a-non-unique-index — Paul Spiegel, Jan 13 '18 at 18:18
Yea the reorder seems to fix it but you are saying that if i do that there is still something wrong with the script, so for example when i will insert some data into the database it will break? — S. Kopecký, Jan 13 '18 at 18:20
And if yes you are saying that the primary keys of **KOPIA_KNIHY** are not unique ? — S. Kopecký, Jan 13 '18 at 18:22
@PaulSpiegel yes but at this example if `KOPIE_ID` is not `UNIQUE`, so since it is `NOT NULL` and already part of a `PRIMARY KEY` adding it as plain `INDEX` might do exactly how much damage? I am aware of what you are trying to explain to the OP and you are for sure correct on that... `But first you need to understand the relations of your tables`. — , Jan 13 '18 at 18:26
@PaulSpiegel in you pervious comment you sai "If ID_KOPIE is UNIQUE it should be the primary key." but isn't ID_KOPIE already a primary key ? — S. Kopecký, Jan 13 '18 at 18:36
@S.Kopecký ID_KOPIE is part of the primary key. It's not the same as to be **the** primary key. It would help if you translate the table and column names. I can just guess that "kniha" is a book and "kopie" is a copy. So if the table name is `book_copy` it seems to be reasonable that `id_copy` is the primary key. — Paul Spiegel, Jan 13 '18 at 18:45
I've translated it now, so what is the difference between `PRIMARY KEY (BOOK_ID, LANGUAGE_CODE, COPY_ID)` and `PRIMARY KEY (COPY_ID, LANGUAGE_CODE, BOOK_ID)` ? — S. Kopecký, Jan 13 '18 at 18:58
Well anyways, i will research some more and thank you for the answers. — S. Kopecký, Jan 13 '18 at 20:52

score 0 · Accepted Answer · 2018-01-13T18:27:00.790

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

.

"The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys." https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html — Paul Spiegel, Jan 13 '18 at 18:02

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

1 Answers1