MySQL ERROR 1215 (HY000)：无法添加外键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22013511/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL ERROR 1215 (HY000): Cannot add foreign key constraint
提问by Lord_DMG
I have looked everywhere about this error and seen plenty of examples and still i cant figure out whats wrong with my script. Im sorry if this is a common issue but searching about it hasnt helped me so far. Here goes the script:
我到处查看有关此错误的信息，并看到了大量示例，但仍然无法弄清楚我的脚本有什么问题。如果这是一个常见问题，我很抱歉，但到目前为止搜索它并没有帮助我。脚本如下：
CREATE DATABASE IF NOT EXISTS ventas;
USE ventas
CREATE TABLE TIENDAS (
nif varchar(10) not null,
nombre varchar(20),
direccion varchar(20),
poblacion varchar(20),
provincia varchar(20) check (provincia = upper(provincia)),
codpostal int(5),
PRIMARY KEY (nif)
) ENGINE=INNODB;
CREATE TABLE FABRICANTES (
cod_fabricante int(3) not null,
nombre varchar(15) check (nombre = upper(nombre)),
pais varchar(15) check (pais = upper(pais)),
PRIMARY KEY (cod_fabricante)
) ENGINE=INNODB;
CREATE TABLE ARTICULOS (
articulo varchar(20) not null,
cod_fabricante int(3) not null,
peso int(3) unsigned not null CHECK (peso > 0),
categoria varchar(10) not null,
precio_venta int(4) unsigned CHECK (precio_venta > 0),
precio_costo int(4) unsigned CHECK (precio_costo > 0),
existencias int(5),
PRIMARY KEY (articulo,cod_fabricante,peso,categoria),
FOREIGN KEY (cod_fabricante) references FABRICANTES (cod_fabricante)
) ENGINE=INNODB;
CREATE TABLE PEDIDOS (
nif varchar(10) not null,
articulo varchar(20) not null,
cod_fabricante int(3) not null,
peso int(3) unsigned not null CHECK (peso > 0),
categoria varchar(10) not null,
fecha_pedido date not null,
unidades_pedidas int(4),
PRIMARY KEY (nif,articulo,cod_fabricante,peso,categoria,fecha_pedido),
FOREIGN KEY (cod_fabricante) references FABRICANTES (cod_fabricante),
FOREIGN KEY (articulo) references ARTICULOS (articulo) ON DELETE CASCADE,
FOREIGN KEY (cod_fabricante) references ARTICULOS (cod_fabricante) ON DELETE CASCADE,
FOREIGN KEY (peso) references ARTICULOS (peso) ON DELETE CASCADE,
FOREIGN KEY (categoria) references ARTICULOS (categoria) ON DELETE CASCADE,
FOREIGN KEY (nif) references TIENDAS (nif)
) ENGINE=INNODB;
Thanks a lot for your help.
非常感谢你的帮助。
回答by Harish Talanki
In your ARTICULOS table, you have multiple columns as primary key .i.e. articulo,cod_fabricante,peso,categoria. In the PEDIDOS table, you are referring a foreign key articulo to ARTUCULOS table's articulo column. Which I think is wrong.
在您的 ARTICULOS 表中，您有多个列作为主键。即 articulo,cod_fabricante,peso,categoria。在 PEDIDOS 表中，您将外键 articulo 引用到 ARTUCULOS 表的 articulo 列。我认为这是错误的。
By the SQL standard, a foreign key must reference either the primary key or a unique key of the parent table. If the primary key has multiple columns, the foreign key must have the same number and order of columns
根据 SQL 标准，外键必须引用父表的主键或唯一键。如果主键有多列，外键的列数和顺序必须相同
