MySQL Forums
Forum List  »  Newbie

ERROR 1512 in adding Foreign Key... but everything looks right
Posted by: George White
Date: August 05, 2017 03:47PM

Hi everyone,

I'm taking an mySQL course and am building a mock travel agency database as proof-of-concept. I'm using mySQL Workbench 6.3 as a development platform.

I have a "FlightInfo" table:

CREATE TABLE `flightinfo` (
`FInumber` int(11) NOT NULL,
`FIFare` double DEFAULT NULL,
`FIDeparts` varchar(3) CHARACTER SET latin1 DEFAULT NULL,
`FIArrives` varchar(3) CHARACTER SET latin1 DEFAULT NULL,
`FIDptTime` int(11) DEFAULT NULL,
`FIArrTime` int(11) DEFAULT NULL,
`FIOperates` varchar(2) CHARACTER SET latin1 DEFAULT NULL,
`FIAircraft` int(11) DEFAULT NULL,
PRIMARY KEY (`FInumber`),
KEY `FIAircraft_idx` (`FIAircraft`),
KEY `FIOperates_idx` (`FIOperates`),
KEY `FIArrives_idx` (`FIArrives`),
KEY `FIDeparts_idx` (`FIDeparts`),
CONSTRAINT `FIAircraft` FOREIGN KEY (`FIAircraft`) REFERENCES `airplanemodels` (`AMNumber`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='replacement for old "flights" and "traverses"';

You'll note I have a foreign key to another table, "AirplaneModels." Here's that table:

CREATE TABLE `airplanemodels` (
`AMNumber` int(11) NOT NULL,
`AMName` varchar(30) DEFAULT NULL,
`AMNumSeats` int(11) DEFAULT NULL,
PRIMARY KEY (`AMNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The foreign key from "FlightInfo" to "AirplaneModels" works great. Here's the problem:

I also need a second foreign key from "FlightInfo," this time to another table, "Airports." Attributes "FIDeparts" and "FIArrives" in "FlightInfo" should both be foreign keys to "APID" (Airport ID) in the "Airports" table. Here's what it looks like:

CREATE TABLE `airports` (
`APID` varchar(3) NOT NULL,
`APName` varchar(55) DEFAULT NULL,
`APCity` varchar(15) DEFAULT NULL,
`APCountry` varchar(20) DEFAULT NULL,
`APRegion` varchar(20) DEFAULT NULL,
PRIMARY KEY (`APID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here's my mySQL statement to add the key:

ALTER TABLE `airline`.`flightinfo`
ADD CONSTRAINT `FIDeparts`
FOREIGN KEY (`FIDeparts`)
REFERENCES `airline`.`airports` (`APID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

The database hates this query:

ERROR 1215: Cannot add foreign key constraint

If I run a "show engine innodb status;" command, the part of the output which I believe is relevant to this issue is here:

------------------------ LATEST FOREIGN KEY ERROR ------------------------
017-08-05 21:35:27 2b81616c8700 Error in foreign key constraint of table airline/#sql-2716_dc7:
FOREIGN KEY (`FIDeparts`) REFERENCES `airline`.`airports` (`APID`)
ON DELETE NO ACTION ON UPDATE NO ACTION:
Cannot find an index in the referenced table where the referenced columns appear as the first columns,
or column types in the table and the referenced table do not match for constraint. Note that the
internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such
columns in old tables cannot be referenced by such columns in new tables.
--------------------------------------------------------------------------

...so... What to make of this? Why does the "FlightInfo" --> "AirplaneModel" foreign key work but the "FlightInfo" --> "Airports" key not go?

Most of the online research I've done about Error 1512 indicates that the data type of the foreign key must exactly match the primary key in the "remote" table. But that shouldn't be a problem here. In "FlightInfo," the "FIDeparts" attribute is a VARCHAR(3) -- in "Airports", "APID" is a VARCHAR(3). The debugging output suggests that the remote primary key must be positioned first, but that also shouldn't be an issue. I'm uncertain what is different between "FlightInfo"s "FIDeparts" and "Airports"s "APID".

Can anyone spot what I'm doing wrong? (Sorry for the long post, wanted to be as specific as possible.)

Options: ReplyQuote


Subject
Written By
Posted
ERROR 1512 in adding Foreign Key... but everything looks right
August 05, 2017 03:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.