MySQL ERROR 1215 (HY000): Cannot add foreign key constraint

Question

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.

Everything looks fine. Please make sure that CHARACTER SET and COLLATION is same for VARCHAR fields. — Deepak Rai, Feb 25 '14 at 12:02
Depending on the version of MySQL you may need to create an index on pedidos columns which you are using as FK — Deepak Rai, Feb 25 '14 at 12:08
A foreign key can only reference a primary or unique column. — Mihai, Feb 25 '14 at 12:08
Please check this link.let me inform `http://stackoverflow.com/questions/18930084/mysql-error-1215-hy000-cannot-add-foreign-key-constraint` — , Feb 25 '14 at 12:09
To get a detailed error: run `SHOW ENGINE INNODB STATUS` and look at the "LATEST FOREIGN KEY ERROR" section — Jared Beck, Apr 01 '15 at 22:24

score 11 · Accepted Answer · answered Feb 25 '14 at 12:08

11

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.

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

answered Feb 25 '14 at 12:08

Harish Talanki

866
13
27

2

THANKS A LOT!! That was the issue. Never would have figured it out without your help. When I changed the PEDIDOS table this way: FOREIGN KEY (articulo,cod_fabricante,peso,categoria) references ARTICULOS (articulo,cod_fabricante,peso,categoria) ON DELETE CASCADE, instead of using split Foreign keys, it worked wonders. – Lord_DMG Feb 25 '14 at 21:36

MySQL ERROR 1215 (HY000): Cannot add foreign key constraint

1 Answers1