Error 1215: MySQL foreign key can not be added

Question

Error 1215: MySQL foreign key can not be added

Navigation

#1 by (3 votes)

3

I am trying to make a Foreign Key to some tables that I have created but MySQL does not allow me, these are the tables:

CREATE TABLE publicador(id_publicador INT NOT NULL PRIMARY KEY AUTO_INCREMENT UNIQUE,
                        fecha_publicador TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                                         ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                        nombre_publicador VARCHAR(20) NOT NULL,
                        apellido_publicador VARCHAR(20),
                        ubicacion_carrito VARCHAR(50),
                        horario_publicador VARCHAR(25) NOT NULL,
                        jornada_publicador TIME);

CREATE TABLE carrito(id_carrito INT NOT NULL PRIMARY KEY AUTO_INCREMENT UNIQUE,
                     fecha_carrito TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                                   ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                     ubicacion_carrito VARCHAR(50) NOT NULL);

CREATE TABLE horario(id_horario INT NOT NULL PRIMARY KEY AUTO_INCREMENT UNIQUE,
                     fecha_horario TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                                   ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                     turno_horario VARCHAR(25) NOT NULL);

CREATE TABLE jornada(id_horario INT NOT NULL PRIMARY KEY AUTO_INCREMENT UNIQUE,
                     fecha_jornada TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                                   ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                     dia_jornada TIME NOT NULL);

CREATE TABLE jornada(id_jornada INT NOT NULL PRIMARY KEY AUTO_INCREMENT UNIQUE,
                     fecha_jornada TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                                   ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                     dia_jornada TIME NOT NULL);

And the following are the foreign key:

ALTER TABLE publicador
   ADD CONSTRAINT fk_publicador_horario
   FOREIGN KEY (horario_publicador)
   REFERENCES horario (turno_horario)
   ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE publicador
   ADD CONSTRAINT fk_publicador_jornada
   FOREIGN KEY (jornada_publicador)
   REFERENCES jornada (dia_jornada)
   ON DELETE CASCADE ON UPDATE CASCADE;

Tables are created correctly, but at the time of applying foreign keys it gives the error that foreign keys can not be applied.

mysql sql

asked by David Calderon 09.12.2016 в 05:25

source

1 answer

error 405 POST ajax laravel MethodNotAllowedHttpException Error: Expression must have a constant value

score 3 · Answer 1

The error occurs because the column to which the foreign key points is not indexed. That is the minimum requirement so that a foreign key can be defined successfully.

For example, if you create the following index:

create index idx_horario_turno_horario on horario(turno_horario);

... you'll see that you can now successfully create the following foreign key:

ALTER TABLE publicador
   ADD CONSTRAINT fk_publicador_horario
   FOREIGN KEY (horario_publicador)
   REFERENCES horario (turno_horario)
   ON DELETE CASCADE ON UPDATE CASCADE;

But, even if this fixes your problem immediately, it would be good to ask yourself if in reality you should define the foreign key using those columns. The most normal is that a foreign key points to a primary or single key.

Therefore, it might be better to redefine the columns in your publicador table so that they point to the primary keys in the horario and jornada tables.

Example:

CREATE TABLE publicador(id_publicador INT NOT NULL PRIMARY KEY AUTO_INCREMENT UNIQUE,
                        fecha_publicador TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                                         ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                        nombre_publicador VARCHAR(20) NOT NULL,
                        apellido_publicador VARCHAR(20),
                        ubicacion_carrito VARCHAR(50),
                        id_horario INT NOT NULL, -- cambiar aquí
                        id_jornada INT);        -- y aquí

... and then define the foreign keys in this way:

ALTER TABLE publicador
   ADD CONSTRAINT fk_publicador_horario
   FOREIGN KEY (id_horario)
   REFERENCES horario (id_horario)
   ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE publicador
   ADD CONSTRAINT fk_publicador_jornada
   FOREIGN KEY (id_jornada)
   REFERENCES jornada (id_jornada)
   ON DELETE CASCADE ON UPDATE CASCADE;

But, of course, it all depends on what the different columns represent. I am only assuming this recommendation, but you will know what is best in this case.