MySQL重复外键约束 [英] Mysql duplicate foreign key constraint
问题描述
当我尝试导入数据库时,出现此错误
When I try to import a database I get this error
SQL query:
ALTER TABLE `bid`
ADD CONSTRAINT `bid_ibfk_4` FOREIGN KEY (`auction_contact_id`) REFERENCES `auction_contact` (`auction_contact_id`),
ADD CONSTRAINT `bid_ibfk_3` FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`)
MySQL said: Documentation
#1826 - Duplicate foreign key constraint name 'projekt_classics/bid_ibfk_3'
查看所有外键,结果就是这样
Looking at all the foreign keys I get this as a result
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY'
结果
def projekt_classics bid_ibfk_2 projekt_classics bid FOREIGN KEY
def projekt_classics bid_ibfk_3 projekt_classics bid FOREIGN KEY
def projekt_classics car_ibfk_1 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_3 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_4 projekt_classics car FOREIGN KEY
def projekt_classics car_brand_ibfk_1 projekt_classics car_brand FOREIGN KEY
在bid_ibfk_3
约束中搜索sql仅显示1次.
所有数据都在导入的数据库中,但我想知道如何避免此错误.
searching into the sql the bid_ibfk_3
constraint shows only 1 time.
All the data is in the imported database but I wonder how I can avoid this error.
首先删除所有表即可运行查询,而不会出现问题. 我使用PHPmyadmin导出数据库. 我猜该错误是由于外键约束在尝试再次创建它之前尚未删除.
First dropping all the tables runs the query without problems. I export my database using PHPmyadmin. I guess the error was because of the foreign key constraint not yet deleted before trying to create it again.
推荐答案
如果查看查询结果,则外键bid_ibfk_3
已存在.实际上,它位于结果的第二行.
If you look at the result of your query, the foreign key bid_ibfk_3
already exists. In fact it is in the second row of the result.
def projekt_classics bid_ibfk_2 projekt_classics bid FOREIGN KEY
--the row below is the foreign key that you are trying to create
def projekt_classics bid_ibfk_3 projekt_classics bid FOREIGN KEY
def projekt_classics car_ibfk_1 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_3 projekt_classics car FOREIGN KEY
def projekt_classics car_ibfk_4 projekt_classics car FOREIGN KEY
def projekt_classics car_brand_ibfk_1 projekt_classics car_brand FOREIGN KEY
这就是为什么在尝试执行此操作时得到重复的外键约束名称的原因:
That's why you are getting the duplicate foreign key constraint name when you are trying to execute this:
ADD CONSTRAINT `bid_ibfk_3` FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`)
您可以修改查询以在实际创建外键之前先检查您要创建的外键是否不存在.
You can modify your query to check first if the foreign key that you are trying to create does not exist, before actually creating it.
IF NOT EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
AND CONSTRAINT_TYPE = 'FOREIGN KEY'
AND CONSTRAINT_NAME = 'bid_ibfk_3') THEN
ALTER TABLE `bid` ADD CONSTRAINT `bid_ibfk_3`
FOREIGN KEY (`car_id`) REFERENCES `car` (`car_id`);
END IF
这篇关于MySQL重复外键约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!