添加外键错误[英] Adding Foreign Key Error

本文是小编为大家收集整理的关于添加外键错误的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我想从表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

推荐答案

简单的一个.

有一行包含无法匹配的客户ID.因此,首先您需要删除/编辑/处理条目并添加外键.

其他推荐答案

CustomerID您正在尝试输入PETS表,在CUSTOMERS表中不存在,这就是为什么您的外键约束失败并引发错误.

您需要确保您在Pets表中输入的客户ID,存在于Customers表中,或者简单地插入NULL NULL PETS.CUSTOMERID字段

其他推荐答案

enterx是正确的.

能够检测到不匹配行:

SELECT * FROM Pet p WHERE (SELECT COUNT(*) FROM Customers c WHERE c.CustomerID=p.CustomerID)=0

只需通过删除以删除MissMatching pet条目来更改选择 *. 您也可以更新pet.customerid为null.但是您必须从宠物表中定义customerId,并使用null选项(而不是null)

本文地址:https://www.itbaoku.cn/post/597315.html

问题描述

I want to add a foreign key from Table Customers, row= "Customer ID" to 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');

This is the code that I have been using to add the foreign key:

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

And the error message from this is:

#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`))

I am quite a beginner with database and I have no idea what I should try next.

I think that's all. Im still new to this stackoverflow so if I missed any necessary information please tell me and I will add it.

UPDATE***

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

I swapped some positions and the error code I recieve is:

#1215 - Cannot add foreign key constraint

推荐答案

Simple one.

There is an row that contains the CustomerID that can't be matched. So first you need to remove/edit/handle the entry and than add a foreign key.

其他推荐答案

The CustomerID you're trying to enter in PETS table, does not exist in CUSTOMERS table, and that is why your Foreign Key constraint fails and throws error.

You need to ensure that the CustomerIDs you're entering in your Pets table, exist in Customers table OR simply insert NULL in the PETS.CUSTOMERID field

其他推荐答案

Enterx is right.

For being able to detect not matching row :

SELECT * FROM Pet p WHERE (SELECT COUNT(*) FROM Customers c WHERE c.CustomerID=p.CustomerID)=0

Just change SELECT * by DELETE for deleting missmatching Pet entry. You can UPDATE Pet.CustomerID to NULL too. But you have to define CustomerID, from Pet table, with NULL option (and not NOT NULL)