我想从表中添加外键Customers
, row= "Customer ID"
到餐桌宠物,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