How to avoid adding duplicate foreign key constraints

Question

I would to know if it is possible to avoid adding several times the same foreign key constraint?

Example: if I execute 3 times the query below, the constraint will exist with 3 times in phpmyadmin... It would be great that it would be rejected the second and third time I apply that query.

ALTER TABLE `my_items_details`
ADD FOREIGN KEY (`items_id`) REFERENCES `my_items`(`item_id`)
ON DELETE RESTRICT
ON UPDATE CASCADE;

Answer 1

You can give a explicit name to the foreign key, instead of letting MySQL assigning a default name for you.

ALTER TABLE `my_items_details`
ADD FOREIGN KEY  `my_foreign_key`  (`item_id`) REFERENCES `my_items`(`item_id`)
ON DELETE RESTRICT
ON UPDATE CASCADE;

Since two objects of the same type cannot have the same name, this query will generate an error the second time you run it:

Error: ER_FK_DUP_NAME: Duplicate foreign key constraint name 'my_foreign_key'

Demo on DB Fiddle