Error Code 1452 When attempting to Alter Table Constraint

Question

ALTER TABLE `charter`
  ADD CONSTRAINT `charter_ibfk_3` FOREIGN KEY (`CHAR_DESTINATION`) 
  REFERENCES `airport` (`AIRPORT_CODE`);

I am attempting to create this relationship:

Charter and Airport ERD Diagram

CREATE TABLE `antonellacammarota` (
  `CUS_CODE` int(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`CUS_CODE`),
  FOREIGN KEY (`CUS_CODE`) REFERENCES `CUSTOMER` (`CUS_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into customer values (10076, "Cammarota", "Antonella", "T", "805", "555-1212", 0);

insert into antonellacammarota(select cus_code from customer where cus_lname = 'Cammarota');

--
-- Table structure for table `airport`
--

CREATE TABLE IF NOT EXISTS `airport` (
  `AIRPORT_CODE` varchar(3) NOT NULL DEFAULT '0',
  `AIRPORT_NAME` varchar(40) DEFAULT NULL,
  `AIRPORT_ADDRESS` varchar(45) DEFAULT NULL,
  `AIRPORT_CITY` varchar(25) DEFAULT NULL,
  `AIRPORT_STATE` char(2) DEFAULT NULL,
  `AIRPORT_ZIP` varchar(10) DEFAULT NULL,
  `AIRPORT_COUNTRY` varchar(25) DEFAULT NULL,
  `AIRPORT_AREACODE` varchar(3) DEFAULT NULL,
  `AIRPORT_PHONE` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`AIRPORT_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Constraints for table `airport`
--
ALTER TABLE `charter`
  ADD CONSTRAINT `charter_ibfk_3` FOREIGN KEY (`CHAR_DESTINATION`) REFERENCES `airport` (`AIRPORT_CODE`);


--
-- Dumping data for table `airport`
--

INSERT INTO `airport` (`AIRPORT_CODE`, `AIRPORT_NAME`, `AIRPORT_ADDRESS`, `AIRPORT_CITY`, `AIRPORT_STATE`, 
`AIRPORT_ZIP`, `AIRPORT_COUNTRY`, `AIRPORT_AREACODE`, `AIRPORT_PHONE`) VALUES
('ATL', 'Hartsfield-Jackson Atlanta Int', '6000 N Terminal Pkwy.', 'Atlanta', 'GA', '30320', 'US', '808', '897-1910'),
('BNA', 'Nashville Int', '1 Terminal Dr.', 'Nashville', 'TN', '37214', 'US', '615', '275-1675'),
('GNV', 'Gainesville Regional', '3880 NE 39th Ave.', 'Gainesville', 'FL', '32609', 'US', '352', '373-0249'),
('MOB', 'Mobile Regional', '8400 Airport Blvd.', 'Mobile', 'AL', '36608', 'US', '800', '357-5373'),
('MOY', 'Monterrey', '', 'Monterrey', '', '', 'Columbia', '', ''),
('STL', 'St. Louis Lambert Int', '10701 Lambert International Blvd.', 'St. Louis', 'MO', '63145', 'US', '314', '426-8000'),
('TYS', 'McGhee Tyson', '2055 Alcoa Hwy', 'Alcoa', 'TN', '37701', 'US', '865', '345-3000');

You likely have bad data in the system that you must handle before you can add the constraint. Likely the FK refers to a PK that no longer exists or never did. — xQbert, Sep 26 '17 at 16:57
I considered that as an option; I have deleted the schema three times now. — student, Sep 26 '17 at 17:05
Please list the content of your `charter` table. `SELECT CHAR_TRIP, CHAR_DESTINATION FROM charter;` — Jacques Amar, Sep 26 '17 at 17:50
10001 ATL 10002 BNA 10003 GNV 10004 STL 10005 ATL 10006 STL 10007 GNV 10008 TYS 10009 GNV 10010 ATL 10011 BNA 10012 MOB 10013 TYS 10014 ATL 10015 GNV 10016 MQY 10017 STL 10018 TYS — student, Sep 26 '17 at 18:23
There are many questions on Stack Overflow that deal with this error. The most common problem is what @xQbert mentioned: some data in your child table has no matching data in the parent table. See https://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa for example — Bill Karwin, Sep 26 '17 at 18:33

score 1 · Accepted Answer · answered Sep 26 '17 at 18:23

1

Use this to find out what entry is screwing things up

SELECT CHAR_TRIP, CHAR_DESTINATION 
FROM charter
WHERE CHAR_DESTINATION NOT IN (
SELECT AIRPORT_CODE FROM airport);

answered Sep 26 '17 at 18:23

Jacques Amar

1,803
1
10
12

10016 MQY was returned; I thought MQY was MOY – student Sep 26 '17 at 18:35
@AntonellaCammarota remember to check this as an accepted answer if it solved the issue for you. – Bill Karwin Sep 26 '17 at 20:20

Error Code 1452 When attempting to Alter Table Constraint

1 Answers1