mysql - 错误代码 :1215 Cannot add foreign key constraint while running script in MySQL workbench version 6. 3

Question

首先，我很抱歉表名等是另一种语言。

问题是由于某种原因，我似乎无法将表 KOPIA_KNIHY 中名为 ID_KOPIE 的外键添加到表 SKLAD 中。当它将外键添加到表 SKLAD 时，它会抛出错误 1215。代码如下:

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),
)

我在互联网上研究了错误代码1215，我找不到我的数据库有任何问题。我检查了是否有拼写错误或者我是否忘记添加引用。

这是错误:

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

我的问题是如何解决这个问题并使其发挥作用。

非常感谢您的帮助。

Answer 1

试试这个方法。请将此示例中的 ON UPDATE ... ON DELETE 语法更改为您需要的语法。

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
);

试试 SQL Fiddle

.