mysql error 1215: Cannot add foreign key constraint// creating this tables

Question

this is my first question here, im trying to create this new database but when i run the script, MySQL sends Error 1215: Cannot add foreign key constraint.

Sorry for my horrible english, im sudamerican. Here is the Code

CREATE TABLE mae_comuna(
comu_id          int PRIMARY KEY AUTO_INCREMENT,
comu_descripcion VARCHAR (20) NOT NULL,
comu_vigencia    CHAR (1) NOT NULL
);

CREATE TABLE mae_region(
reg_id int   AUTO_INCREMENT PRIMARY KEY,
reg_region   VARCHAR (20) NOT NULL ,
reg_vigencia CHAR (1) NOT NULL
) ;

CREATE TABLE mae_tipo_direccion(
tipo_direc_id          INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
tipo_direc_descripcion VARCHAR (20) NOT NULL ,
tipo_direc_vigencia    CHAR (1) NOT NULL
) ;

CREATE TABLE mae_tipo_persona(
tipo_per_id          INT NOT NULL PRIMARY KEY AUTO_INCREMENT ,
tipo_per_descripcion VARCHAR (50) NOT NULL ,
tipo_per_vigencia    CHAR (1) NOT NULL
);

CREATE TABLE zs_usuario(
usu_id             INT PRIMARY KEY AUTO_INCREMENT ,
usu_nombre_usuario VARCHAR (50) NOT NULL ,
usu_contraseña     VARCHAR (15) NOT NULL ,
usu_vigencia       CHAR (1) NOT NULL
) ;

CREATE TABLE zs_camionero(
cami_id             INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
cami_tipo_camionero VARCHAR (10) NOT NULL,
cami_vigencia       CHAR (1) NOT NULL
) ;

CREATE TABLE zs_cliente(
cli_rut            INT NOT NULL  PRIMARY KEY,
cli_dv             CHAR (1) NOT NULL ,
cli_tarifa_peso    FLOAT NOT NULL,
cli_tarifa_volumen FLOAT NOT NULL,
cli_vigencia       CHAR (1) NOT NULL
) ;

CREATE TABLE zs_persona(
per_id                       INT PRIMARY KEY AUTO_INCREMENT ,
per_rut                      INT NOT NULL ,
per_dv                       CHAR (1) NOT NULL,
per_nombres                  VARCHAR (30) NOT NULL ,
per_apellido_aterno          VARCHAR (15) NOT NULL ,
per_apellido_materno         VARCHAR (15) NOT NULL ,
per_nombre_completo          VARCHAR (70) NOT NULL ,    
per_fecha_nacimiento         DATE,
per_fecha_registro           DATE,
per_sexo                     CHAR (1) NOT NULL ,
per_telefono                 INT NOT NULL,
per_email                    VARCHAR(50) NOT NULL,
per_vigencia                 CHAR (1) NOT NULL ,
tipo_per_id                  INT NOT NULL,
usu_id                       INT,
cli_id                       INT,
per_id                       INT,
cami_id                      INT,    
FOREIGN KEY (tipo_per_id) REFERENCES mae_tipo_persona(per_id),
FOREIGN KEY (usu_id) REFERENCES zs_usuario(usu_id),
FOREIGN KEY (cli_id) REFERENCES zs_cliente(per_id),
FOREIGN KEY (cami_id) REFERENCES zs_camionero(cami_id)    
) ;

CREATE TABLE zs_direccion(
dir_id        INT NOT NULL PRIMARY KEY AUTO_INCREMENT ,
dir_direccion VARCHAR (50) NOT NULL ,
dir_numero         INT,
dir_vigencia       CHAR (1) NOT NULL,      
comu_id INT,
per_id  INT,
reg_id  INT,
FOREIGN KEY (comu_id) REFERENCES mae_comuna(comu_id),
FOREIGN KEY (per_id) REFERENCES zs_persona(per_id),
FOREIGN KEY (reg_id) REFERENCES mae_region(reg_id)
) ;

CREATE TABLE zs_manifiesto_carga(
mc_numero_manifiesto INT PRIMARY KEY AUTO_INCREMENT,
mc_fecha_salida      DATE NOT NULL,
mc_fecha_embarque    DATE NOT NULL,
mc_patente_camion    VARCHAR (7) NOT NULL,
mc_patente_rampla    VARCHAR (7) NOT NULL,
mc_tramo             VARCHAR (30) NOT NULL,
mc_fecha_llegada     DATE NOT NULL ,
mc_total_neto        FLOAT NOT NULL ,
mc_iva               FLOAT NOT NULL ,
mc_total_con_iva     FLOAT NOT NULL ,
mc_factura_interna   INT NOT NULL,
mc_observaciones     VARCHAR (100) NOT NULL ,
mc_vigente           CHAR (1) NOT NULL
) ;

CREATE TABLE zs_orden_retiro(
or_numero_orden          INT PRIMARY KEY AUTO_INCREMENT,
or_numero_factura_flete  BIGINT NOT NULL,
or_fecha_actual          DATE NOT NULL,
or_rampla                VARCHAR (10),
or_hora_llegada          DATE NOT NULL,
or_retiro                CHAR (1) NOT NULL,
or_estado_pago           CHAR (1) NOT NULL,
or_factura_cliente       BIGINT NOT NULL,
or_guia_despacho_cliente BIGINT NOT NULL,
or_cantidad_bultos       INT NOT NULL,
or_contenido_sin_revisar CHAR (1) NOT NULL ,
or_peso                  FLOAT NOT NULL ,
or_volumen               FLOAT NOT NULL ,
or_descripcion_carga     VARCHAR (100),
or_notas                 VARCHAR (100),
or_vigencia              CHAR (1) NOT NULL,      
per_id                   INT NOT NULL,
mc_numero_manifiesto     INT NOT NULL,
FOREIGN KEY (per_id) REFERENCES zs_persona(per_id),
FOREIGN KEY (mc_numero_manifiesto) REFERENCES 
zs_manifiesto_carga(mc_numero_manifiesto)
) ;

Thanks for your help guys!

Possible duplicate of [MySQL Error 1215: Cannot add foreign key constraint](http://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint) — Didier L, Apr 12 '17 at 19:50

score 0 · Accepted Answer · answered Apr 12 '17 at 18:05

Given the order in which your CREATE TABLE statements execute, you are trying to add some foreign keys before you create the table they reference.

The solution is to run these commands before executing your CREATE TABLE statements:

set @foreign_key_checks = @@foreign_key_checks;
set foreign_key_checks = 0;

Then you can revert that setting after creating the tables:

set foreign_key_checks = @foreign_key_checks;

mysql error 1215: Cannot add foreign key constraint// creating this tables

1 Answers1