How can I get around this foreign key constraint having to have a unique name?

Question

I'm not sure why these have to be unique, but from reading the MySQL forums it appears that they do. However, I think it has something more to do with the INDEX name. I have two tables that have foreign key constraints referencing the same primary key on a third table. If it helps, I'm using MySQL workbench to design the schema.

I usually name my foreign key on each table the same name as the primary key it references. I guess this isn't possible. It will create the first table with the foreign key constraint, but when it tries to create the second table it throws an error. Here is the second table it throws the error on:

CREATE  TABLE IF NOT EXISTS `joe`.`products_to_categories` (
  `product_to_category_id` INT NOT NULL AUTO_INCREMENT ,
  `category_id` INT NOT NULL ,
  `product_id` INT NOT NULL ,
  PRIMARY KEY (`product_to_category_id`) ,
  INDEX `category_id` (`category_id` ASC) ,
  INDEX `product_id` (`product_id` ASC) ,
  CONSTRAINT `category_id`
    FOREIGN KEY (`category_id` )
    REFERENCES `joe`.`categories` (`category_id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `product_id`
    FOREIGN KEY (`product_id` )
    REFERENCES `joe`.`products` (`product_id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

I want the foreign key names to be the same as the primary key in both of the other tables. What should I remove here so that I can use these names. What is the best practice here.

Answer 1

It is not possible because you would have a conflict in the filename for the file that is used for the index IIRC. I probably would name the key < tablename >_< column_name > or something like that.

Answer 2

In PostgreSQL, the default for naming indexes is to append "_pkey" and "_fkey" to the name of the primary and foreign key, respectively. So your case would look like:

INDEX `product_id_fkey` (`product_id` ASC) ,

UPDATE: I just tried this and it worked. See if that's what you had in mind.

    use test;

create table if not exists test.product
(
    product_id int not null auto_increment,
    name varchar(80) not null,
    primary key(product_id)
);

create table if not exists test.category
(
    category_id int not null auto_increment,
    name varchar(80) not null,
    primary key(category_id)
);

create table if not exists test.product_category
(
    product_id int,
    category_id int,
    primary key(product_id, category_id),
    constraint product_id_fkey
        foreign key(product_id) references product(product_id)
        on delete cascade
        on update no action,
    constraint category_id_fkey
        foreign key(category_id) references category(category_id)
        on delete cascade
        on update no action
);

insert into test.product(name) values('teddy bear');
insert into test.category(name) values('toy');
insert into test.product_category 
    select p.product_id, c.category_id from product as p, category as c
    where p.name = 'teddy bear' and c.name = 'toy';

Answer 3

You are creating an index (constraint) by the name of product_id via: INDEX product_id

Then you are going and creating another constraint (for the foreign key) with the same name: CONSTRAINT product_id

What you need to do is allow the server to provide a default, unique constraint name by removing the CONSTRAINT product_id

See this URL: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

"If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically."