mysql - 添加外键错误

Question

我想将表 Customers, row= "Customer ID" 中的外键添加到 Table Pet, row= "Customer ID" >.

-- Table structure for table `Customers`

CREATE TABLE IF NOT EXISTS `Customers` (
  `CustomerID` varchar(50) NOT NULL,
  `Fname` varchar(50) DEFAULT NULL,
  `LName` varchar(20) DEFAULT NULL,
  `Tel` varchar(20) DEFAULT NULL,
  `Fax` varchar(20) DEFAULT NULL,
  `CustType` varchar(20) DEFAULT NULL,
  `AdState` varchar(50) DEFAULT NULL,
  `City` varchar(20) DEFAULT NULL,
  `Zip` varchar(20) DEFAULT NULL,
  `Street` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table `Customers`

INSERT INTO `Customers` (`CustomerID`, `Fname`, `LName`, `Tel`, `Fax`, `CustType`, `AdState`, `City`, `Zip`, `Street`) VALUES
('AC001', 'All', 'Creatures', '206 555-6622', '206 555-7854', '2', 'WA', 'Tall Pines', '98746', '21 Grace St.'),
('AD001', 'Johnathan', 'Adams', '206 555 7623', '206 555 8855', '1', 'WA', 'Mountain View', '984101012', '66 10th St'),
('AD002', 'William', 'Adams', '503 555 7623', '503 555 7319', '1', 'OR', 'Lakewille', '9740110011', '1122 10th_St'),
('AK001', 'Animal', 'Kingdom', '208 555 7108', '', '2', 'ID', 'Borderville', '834835646', '15 Marlin Lane');

CREATE TABLE IF NOT EXISTS `Pet` (
  `ID` varchar(50) NOT NULL,
  `CustomerID` varchar(50) NOT NULL,
  `Gender` varchar(20) DEFAULT NULL,
  `Race` varchar(20) DEFAULT NULL,
  `Name` varchar(20) DEFAULT NULL,
  `Kind` varchar(20) DEFAULT NULL,
  `Birthday` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Dumping data for table `Pet`

INSERT INTO `Pet` (`ID`, `CustomerID`, `Gender`, `Race`, `Name`, `Kind`, `Birthday`) VALUES
('AC001-01', '0', 'M', 'Long Ear', 'Bobo', 'Rabbit', '4/8/92'),
('AC001-02', '0', 'F', 'Chameleon', 'Presto Chango', 'Lizard', '5/1/92'),
('AC001-03', '0', 'M', '', 'Stinky', 'Skunk', '8/1/91'),
('AC001-04', '0', 'M', 'German Shepherd', 'Fido', 'Dog', '6/1/90'),
('AD001-01', '0', 'F', 'Potbelly', 'Patty', 'Pig', '2/15/91'),
('AD001-02', '0', 'M', 'Palomino', 'Rising Sun', 'Horse', '4/10/90'),
('AD002-01', '0', 'F', 'Mixed', 'Dee Dee', 'Dog', '2/15/91'),
('AK001-03', '0', 'M', '', 'Jerry', 'Rat', '2/1/88'),
('AK001-07', '0', 'M', 'Beagle', 'Luigi', 'Dog', '8/1/92');

这是我用来添加外键的代码:

ALTER TABLE Pet ADD CONSTRAINT Pet_FK 
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID);

错误消息是:

#1452 - Cannot add or update a child row: a foreign key constraint fails     
(`hospital`.`#sql-523_76e`, CONSTRAINT `Pet_FK` FOREIGN KEY (`CustomerID`) 
REFERENCES `Customers` (`CustomerID`))

我是数据库的初学者，我不知道下一步应该尝试什么。

我想就这些了。我对这个 stackoverflow 还很陌生，所以如果我错过了任何必要的信息，请告诉我，我会添加它。

更新***

ALTER TABLE Customers ADD CONSTRAINT Customers_FK 
FOREIGN KEY (CustomerID) REFERENCES Pet (CustomerID);

我交换了一些位置，收到的错误代码是:

#1215 - Cannot add foreign key constraint

Answer 1

简单的一个。

有一行包含无法匹配的 CustomerID。因此，首先您需要删除/编辑/处理该条目，然后添加外键。