Executing SQL script in server
ERROR: Error 1215: Cannot add foreign key constraint
SQL Code:
-- -----------------------------------------------------
-- Table `MeliaDTB`.`hotel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `MeliaDTB`.`hotel` (
`ID` INT NOT NULL,
`nombre` VARCHAR(45) NOT NULL,
`gerente` VARCHAR(45) NOT NULL,
`dir_postal` VARCHAR(45) NOT NULL,
`ciudad` VARCHAR(45) NOT NULL,
`cadena` INT NOT NULL,
`estrellas` INT NOT NULL,
PRIMARY KEY (`ID`),
INDEX `fk_hotel_ciudad1_idx` (`ciudad` ASC),
INDEX `fk_hotel_cadena_hotelera1_idx` (`cadena` ASC),
CONSTRAINT `fk_hotel_ciudad1`
FOREIGN KEY (`ciudad`)
REFERENCES `MeliaDTB`.`ciudad` (`nombre`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_hotel_cadena_hotelera1`
FOREIGN KEY (`cadena`)
REFERENCES `MeliaDTB`.`cadena_hotelera` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
SQL script execution finished: statements: 11 succeeded, 1 failed
Fetching back view definitions in final form.
Nothing to fetch
Complete list of tables:
As you can see, I get an error in the table hotel
, but I don't see the problem. I had been thinking that it was because I was taking VARCHAR values from another table, but it gives me the same problem 1215 if I take INT (primary key)
Short answer
First you have to add an index to the column
nombre
in the tableciudad
:This is because InnoDB requires that the referenced column or group of columns have an index on them (something that is not really supported by the SQL standard, but is instead an InnoDB extension).
long answer
The 1215 error in MySQL is simply "cannot add foreign key". This can be due to different reasons, the most frequent that I have seen are:
This last point is the one that is almost certainly causing the error. The problem will be that you don't have any index defined on the table
ciudad
for the fieldnombre
. Then the definition of the foreign keyfk_hotel_ciudad1_idx
will fail ( the definition of willfk_hotel_cadena_hotelera1_idx
work correctly because it is referencing a field that is a primary key).As a recommendation, I would consider changing your database structure a bit and make foreign keys point to primary or unique keys. Although you then have to do joins to get the value of the city name, they will make the database more robust and easier to maintain (and migrate).