CODEWITHSUNDEEP
mysqlsql
Matias Celiz
Matias Celiz

Reputation: 322

SQL - Foreign key constraint is incorrectly formed

I don't know what's the problem in this code... I verified all columns names and data types but this not work

CREATE TABLE empleado (
    tipo_dni VARCHAR(50) NOT NULL,
    nro_dni INT NOT NULL,
    nombre VARCHAR(50) NOT NULL,
    apellido VARCHAR(50) NOT NULL,
    direccion VARCHAR(50) NOT NULL,
    telefono INT NOT NULL,
    id_ciudad INT NOT NULL,
    PRIMARY KEY (tipo_dni , nro_dni)
);

CREATE TABLE director (
    tipo_dni VARCHAR(50) NOT NULL,
    nro_dni INT NOT NULL,
    PRIMARY KEY (tipo_dni, nro_dni),
    FOREIGN KEY (tipo_dni)
        REFERENCES empleado (tipo_dni),
    FOREIGN KEY (nro_dni)
        REFERENCES empleado (nro_dni)
);

ERROR

#1005 - Can't create table `tpfinal`.`director` (errno: 150 "Foreign key constraint is incorrectly formed")

Any idea ?

Upvotes: 1

Views: 212

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You primary key is:

PRIMARY KEY (tipo_dni , nro_dni)

This is a composite primary key. The foreign key reference should be composite as well:

FOREIGN KEY (tipo_dni, nro_dni)
    REFERENCES empleado (tipo_dni, nro_dni)

Upvotes: 2

Related Questions