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;
Advertisement
Answer
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’