phpmyadmin Question posted in
The official documentation can be found here.

How to avoid adding duplicate foreign key constraints – Phpmyadmin

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;
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    To prevent duplicate foreign key values in a table in a database, you can define a unique index or unique constraint on the foreign key field in the table.

    In PHPMyAdmin, you can create a unique index or constraint on a foreign key field by following these steps:

    Select the table from the list of tables in the left-hand panel.
    Click on the "Structure" tab.
    Scroll down to the field that you want to define the unique index or constraint on.
    Click on the "Index" drop-down menu for the field, and select "Unique".
    Alternatively, you can use the following SQL query to create a unique index on a foreign key field called item_id in a table called items:

    ALTER TABLE items ADD UNIQUE (item_id);

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search