外键约束失败

Question

I have the following tables:

CREATE TABLE IF NOT EXISTS `Person_Categories` (
  `PrsCatID` int(11) NOT NULL auto_increment,
  `PrsCategory` varchar(45) NOT NULL,
  PRIMARY KEY  (`PrsCatID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

CREATE TABLE IF NOT EXISTS `Persons` (
  `PersonID` int(11) NOT NULL auto_increment,
  `FirstName` varchar(45) NOT NULL,
  `LastName` varchar(45) NOT NULL,
  `OrderName` varchar(45) default NULL,
  `Email` varchar(45) NOT NULL,
  `Telephone` varchar(20) default NULL,
  `Mobile` varchar(20) default NULL,
  `StreetAddress` varchar(45) NOT NULL,
  `City` varchar(45) NOT NULL,
  `RegionID` int(2) NOT NULL,
  `PostCode` varchar(10) NOT NULL,
  `CountryID` int(11) NOT NULL,
  `TitleID` int(11) NOT NULL,
  `CIC_MailingList` tinyint(1) NOT NULL,
  `FoundationMember` tinyint(1) NOT NULL,
  `PersonCmts` mediumtext,
  PRIMARY KEY  (`PersonID`),
  KEY `TitleID` (`TitleID`),
  KEY `RegionID` (`RegionID`),
  KEY `CountryID` (`CountryID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;


CREATE TABLE IF NOT EXISTS `Persons_PersonCategories` (
  `PersonID` int(11) NOT NULL,
  `PrsCatID` int(11) NOT NULL,
  PRIMARY KEY  (`PersonID`,`PrsCatID`),
  KEY `PrsCatID` (`PrsCatID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 Constraints for the tables
--
ALTER TABLE `Persons`
  ADD CONSTRAINT `Persons_ibfk_12` FOREIGN KEY (`TitleID`) REFERENCES `Job_Titles` (`TitleID`),
  ADD CONSTRAINT `Persons_ibfk_14` FOREIGN KEY (`CountryID`) REFERENCES `Countries` (`CountryID`),
  ADD CONSTRAINT `Persons_ibfk_15` FOREIGN KEY (`RegionID`) REFERENCES `Regions` (`RegionID`);

ALTER TABLE `Persons_PersonCategories`
  ADD CONSTRAINT `Persons_PersonCategories_ibfk_8` FOREIGN KEY (`PrsCatID`) REFERENCES `Person_Categories` (`PrsCatID`),
  ADD CONSTRAINT `Persons_PersonCategories_ibfk_7` FOREIGN KEY (`PersonID`) REFERENCES `Persons` (`PersonID`);

Persons_PersonCateogires is a linking table for a n:m relationship. When I try to insert PersonID and PrsCatID into the Persons_PersonCategories via my php application I get the following error:

An error occurs during insert:

Cannot add or update a child row: a foreign key constraint fails (ubarry09_andrew/Persons_PersonCategories, CONSTRAINT Persons_PersonCategories_ibfk_7 FOREIGN KEY (PersonID) REFERENCES Persons (PersonID))

Here is the insert statement:

INSERT INTO Persons_PersonCategories
VALUES (PersonID, PrsCatID)

Persons and Persons_Categories tables are populated with data.

Many thanks, zan

Answer 1

The following SQL statement is legal, but it probably doesn't do what you intend:

INSERT INTO Persons_PersonCategories
VALUES (PersonID, PrsCatID);

This basically tries to insert (NULL, NULL) because it evaluates the expressions in the VALUES clause before it creates the row. But the expressions name columns within the context of the yet-to-be-created row, so there are no values to use. Thus it uses NULL for both.

NULLs are not allowed in PRIMARY KEY columns, and MySQL automatically promotes NULL values when you use them for primary key columns. In this case it promotes them to the integer value 0. There is no value 0 in the Persons and PersonCategories tables, so you get an FK error.

Try this experiment:

CREATE TABLE IF NOT EXISTS `Persons_PersonCategories2` (
  `PersonID` int(11) NOT NULL,
  `PrsCatID` int(11) NOT NULL,
  PRIMARY KEY  (`PersonID`,`PrsCatID`),
  KEY `PrsCatID` (`PrsCatID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

That is, a table like the one you created, but with no FK constraints.

INSERT INTO Persons_PersonCategories2
VALUES (PersonID, PrsCatID);

SELECT * FROM Persons_PersonCategories2;
+----------+----------+
| PersonID | PrsCatID |
+----------+----------+
|        0 |        0 |
+----------+----------+

---

You get foreign key constraint errors when you try to insert a value in an FK column that doesn't exist in the referenced PK column of the parent. Zero is typically not used by auto-increment primary keys, so it's bound to fail.

What you need to do is provide this INSERT with the primary values from the respective tables you want to reference. Like this:

$stmt = $pdo->prepare("INSERT INTO Persons_PersonCategories (PersonID, PrsCatID) 
    VALUES (?, ?)");

Using prepared statements is easy and it helps to protect against SQL injection mistakes. Then supply values from the other tables when you execute. These values are automatically bound to the ? placeholders you used in the prepared query:

$stmt->execute( array(1234, 5678) );

As long as those values 1234 and 5678 correspond to existing rows in your referenced tables Persons and PersonCategories, the foreign key constraint will be satisfied.