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屋！