Mysql duplicate foreign key constraint

Question

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' 

result

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

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.

EDIT: 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.

Answer 1

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