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
– Deepak Rai, Commented Feb 25, 2014 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
– Deepak Rai, Commented Feb 25, 2014 at 12:08
A foreign key can only reference a primary or unique column. — Mihai
– Mihai, Commented Feb 25, 2014 at 12:08
Please check this link.let me inform http://stackoverflow.com/questions/18930084/mysql-error-1215-hy000-cannot-add-foreign-key-constraint — user3177895
– user3177895, Commented Feb 25, 2014 at 12:09

Harish Talanki · Accepted Answer · 2014-02-25 12:08:35Z

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, 2014 at 12:08

Harish Talanki

87613 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lord_DMG Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related