Error #1215 - Cannot add foreign key constraint ON DELETE SET NULL

Question

I have been trying for a couple of hours to add this query with referential integrity but it indicates an error, but if I remove them, the query runs well. I only set them on lotes and operaciones tables.

CREATE DATABASE alamilla;
USE alamilla;


CREATE TABLE cat_niveles(
    id INT(11) AUTO_INCREMENT,
    nombre VARCHAR(255),
    PRIMARY KEY (id) 
)ENGINE=INNODB;

INSERT INTO cat_niveles VALUES(NULL, 'ADMINISTRADOR');
INSERT INTO cat_niveles VALUES(NULL, 'VENTA');
INSERT INTO cat_niveles VALUES(NULL, 'ALTA');


CREATE TABLE categorias_medicamentos(
    id INT(11) AUTO_INCREMENT,
    nombre VARCHAR(255),
    PRIMARY KEY(id)
)ENGINE=INNODB;

CREATE TABLE corte_cajas(
    id INT(11) AUTO_INCREMENT,
    fecha_corte DATETIME,
    PRIMARY KEY(id)
)ENGINE=INNODB;

CREATE TABLE proveedores(
    id INT(11) AUTO_INCREMENT,
    nombre VARCHAR(255) NOT NULL,
    apellidos VARCHAR(255) NOT NULL,
    compania VARCHAR(100) NOT NULL,
    direccion VARCHAR(100) NULL,
    telefono1  VARCHAR(100) NULL,
    telefono2 VARCHAR(100) NULL,
    fecha_alta DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id)
)ENGINE=INNODB;

CREATE TABLE medicamentos(
    cve_medicamento  VARCHAR(255) NOT NULL,
    nombre_generico  VARCHAR(255) NOT NULL,
    nombre_comercial VARCHAR(255) NULL,
    descripcion      VARCHAR(255) NULL,
    imagen           VARCHAR(255) NULL,
    presentacion     VARCHAR(255) NULL,
    precio_adquisitivo DECIMAL(15,2) NOT NULL,
    precio_venta DECIMAL(15,2) NOT NULL,
    unidades_caja INT(11) NOT NULL,
    stock_minimo INT(11) NOT NULL,
    fecha_alta DATETIME DEFAULT CURRENT_TIMESTAMP,
    id_proveedor INT(11) NULL,
    id_categoria INT(11) NULL,
    PRIMARY KEY(cve_medicamento),
    FOREIGN KEY(id_proveedor) REFERENCES proveedores(id)

)ENGINE=INNODB;

CREATE TABLE tipo_operaciones(  
    id INT(11) AUTO_INCREMENT,
    nombre VARCHAR(255) NOT NULL,
     PRIMARY KEY(id)
)ENGINE=INNODB;

CREATE TABLE lotes(
    cve_lote VARCHAR(255) NOT NULL,
    fecha_alta DATETIME DEFAULT CURRENT_TIMESTAMP,
    fecha_caducidad DATE,
    cve_medicamento  VARCHAR(255) NOT NULL,
    PRIMARY KEY(cve_lote),
    FOREIGN KEY(cve_medicamento) REFERENCES medicamentos(cve_medicamento) ON DELETE SET NULL ON UPDATE CASCADE
)ENGINE=INNODB;


CREATE TABLE pedidos(
    id INT(11) AUTO_INCREMENT,
    dinero DECIMAL(15, 2) NULL,
    total DECIMAL(15,2) NULL,
    descuento DECIMAL(15, 2) NULL,
    fecha     DATETIME NULL,
    id_tipo_operacion INT(11) NULL,
    id_proveedor INT(11) NULL,
    id_corte_caja      INT(11) NULL,
    id_usuario   INT(11) NULL,
     PRIMARY KEY(id),
    FOREIGN KEY(id_tipo_operacion) REFERENCES tipo_operaciones(id),
    FOREIGN KEY(id_proveedor) REFERENCES proveedores(id),
    FOREIGN KEY(id_corte_caja) REFERENCES corte_cajas(id)
)ENGINE=INNODB;


CREATE TABLE operaciones(
    id INT(11) AUTO_INCREMENT,
    cantidad INT(11) NULL,
    descuento DECIMAL(15,2) NULL,
    precio DECIMAL(15,2) NULL,
    id_tipo_operacion INT(11) NULL,
    id_medicamento   VARCHAR(255) NULL,
    id_pedido      INT(11) NULL,
    cve_lote       VARCHAR(255) NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(id_tipo_operacion) REFERENCES tipo_operaciones(id),
    FOREIGN KEY(id_medicamento) REFERENCES medicamentos(cve_medicamento) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY(id_pedido) REFERENCES pedidos(id),
    FOREIGN KEY(cve_lote) REFERENCES lotes(cve_lote) ON DELETE SET NULL ON UPDATE CASCADE
)ENGINE=INNODB;

CREATE TABLE usuarios(
    id INT(11) AUTO_INCREMENT,
    nick VARCHAR(100) NOT NULL,
    pass VARCHAR(255) NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    apellidos VARCHAR(100) NOT NULL,
    bloque TINYINT(2) NOT NULL,
    foto VARCHAR(255),
    id_nivel INT(11) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(id_nivel) REFERENCES cat_niveles(id)
)ENGINE=INNODB;

INSERT INTO usuarios VALUES(NULL, 'admin','d033e22ae348aeb5660fc2140aec35850c4da997','ALBERTO','ALAMILLA MURILLO', 1, 'foto_perfil/ADMINISTRADOR.png', 1);

INSERT INTO `categorias_medicamentos` (`id`, `nombre`) VALUES
(null, 'BAÃ‘OS'),
(null, 'COLUTORIOS'),
(null, 'COMPRIMIDOS'),
(null, 'ELIXIRES'),
(null, 'GOTAS'),
(null, 'GOTAS NASALES'),
(null, 'INHALADORES'),
(null, 'JARABE'),
(null, 'LOCIONES'),
(null, 'MEDICAMENTO DE PRUEBA'),
(null, 'NEBULIZACIONES'),
(null, 'PARCHES TRANDERMICOS'),
(null, 'POLVOS'),
(null, 'POMADAS RECTALES'),
(null, 'SELLOS'),
(null, 'SUPOSITORIOS'),
(null, 'SUSPENSION'),
(null, 'TABLETAS');

I always get the error # 1215 can not add foreign key constraint A hand would not be a bad idea, thanks. PS: I already have a headache because I did not find the problem

Which lines/foreign keys are specifically causing the problem? Can you explicitly point them out (maybe with a `-- comment`)? — TrebledJ, Nov 27 '18 at 01:43
Possible duplicate of [MySQL Error 1215: Cannot add foreign key constraint](https://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint) — philipxy, Nov 27 '18 at 05:15
Hi. (As we can clearly expect,) This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. — philipxy, Nov 27 '18 at 05:16
A [mcve] includes DBMS (with version), a clear specification, cut & paste & runnable code (with small representative input) (format table initializations as tables) & diffable (hence ordered) desired output. But absolute basics of debugging say: Show that your program does what you expect as it goes through (sub)expressions by saying what that is & showing that it actually does it via incremental output. On adding problem code that you can't fix, research (the manual & the web). Repeat, minimizing working & wrong code. Then ask re the (small) difference between working & non-working examples. — philipxy, Nov 27 '18 at 05:18
Possible duplicate of [ERROR 1215: Cannot add foreign key constraint when using ON DELETE SET NULL](https://stackoverflow.com/questions/16608042/error-1215-cannot-add-foreign-key-constraint-when-using-on-delete-set-null) @philipxy the duplicate question you've found answers a different foreign-key question. — TrebledJ, Nov 27 '18 at 06:57
@TrebuchetMS Thanks. Point 5 of the 2nd answer of the link I gave answers this. I don't much care whether that post's code has the exact same problem as this post's code since the non-code in both is so vague that they are the same question. I'm not much motivated to find a duplicate to an obvious faq that clearly wasn't researched well. — philipxy, Nov 27 '18 at 07:20
Haha, [look at all these.](https://stackoverflow.com/search?q=mysql+cannot+add+foreign+key+error) There's got to be at least 50 error 1215's. Maybe make a centralised FAQ/wiki? — TrebledJ, Nov 27 '18 at 07:23
@TrebuchetMS Thanks. Ha mine is #1. I'm not sure why I even bothered to search, I suppose to show the asker how easiiy found an answer was, I think I already close-voted per MCVE or too broad. PS Please don't answer questions that should be closed. — philipxy, Nov 27 '18 at 07:32

TrebledJ · Accepted Answer · 2018-11-27T06:52:46.400

There is a contradiction in your CREATE statement for the lotes table.

CREATE TABLE lotes(
    cve_lote VARCHAR(255) NOT NULL,
    fecha_alta DATETIME DEFAULT CURRENT_TIMESTAMP,
    fecha_caducidad DATE,
    cve_medicamento  VARCHAR(255) NOT NULL,
    PRIMARY KEY(cve_lote),
    FOREIGN KEY(cve_medicamento) REFERENCES medicamentos(cve_medicamento) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=INNODB;

The trouble lies here:

cve_medicamento  VARCHAR(255) NOT NULL

And here:

FOREIGN KEY(cve_medicamento) ... ON DELETE SET NULL

In the first line, you've explicitly stated that cve_medicamento shouldn't be NULL. On the second line, you've explicitly stated that cve_medicamento should be set to NULL when its foreign-key reference is deleted.

I was able to get the query to successfully create a lotes table by eliminating the NOT NULL and changing the line to

cve_medicamento  VARCHAR(255)

Read more from the MySQL documentation....

If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

Bro I didn't even notice it in the first place. Thank you so much I really was so tired That I couldnt see the issue. Have a good one. — Javier Quiroga Almeida, Nov 27 '18 at 04:36

Error #1215 - Cannot add foreign key constraint ON DELETE SET NULL

1 Answers1