MySQL ERROR 1215（HY000）：无法添加外键约束 [英] MySQL ERROR 1215 (HY000): Cannot add foreign key constraint

问题描述

我已经看了无处不在的这个错误，看到很多例子，仍然我不能弄清楚什么是我的脚本错误。对不起，如果这是一个常见的问题，但搜索它没有帮助我到目前为止。这里是脚本：

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;

非常感谢您的帮助。

推荐答案

在ARTICULOS表中，您有多个列作为主键.ie articulo，cod_fabricante，peso，categoria。在PEDIDOS表中，您将外键指向ARTUCULOS表的关节列。我认为是错误的。

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.

根据SQL标准，外键必须引用父表或父表的唯一键。如果主键有多个列，则外键必须具有相同的列数和顺序

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

这篇关于MySQL ERROR 1215（HY000）：无法添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！