MySQL: Creating table with two foreign keys fails with "Duplicate key name" error

Question

While trying to create a new table, MySQL is giving me an error I cannot explain.

CREATE TABLE Products (
    id INT NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE WarehouseMovements (
    time        DATETIME           NOT NULL,
    product1    INT                NOT NULL,
    product2    INT                NOT NULL,

    FOREIGN KEY WarehouseMovements(product1) REFERENCES Products(id),
    FOREIGN KEY WarehouseMovements(product2) REFERENCES Products(id)
);

This fails with ERROR 1061 (42000): Duplicate key name 'WarehouseMovements'. If I remove the foreign key constraints this succeeds, but I want to make sure product1 and product2 are actually pointing to somewhere.

Default engine is InnoDB.

What's wrong with the query?

Answer 1

Try making the FK names distinct:

CREATE TABLE Products (
    id INT NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE WarehouseMovements (
    time        DATETIME           NOT NULL,
    product1    INT                NOT NULL,
    product2    INT                NOT NULL,

    FOREIGN KEY IX_WarehouseMovements_product1(product1) REFERENCES Products(id),
    FOREIGN KEY IX_WarehouseMovements_product2(product2) REFERENCES Products(id)
);

UPDATE

That's an index or FK name, not a table name. See create-table-foreign-keys documentation:

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)

Which says

index_name represents a foreign key ID. If given, this is ignored if an index for the foreign key is defined explicitly. Otherwise, if MySQL creates an index for the foreign key, it uses index_name for the index name.

Answer 2

you must to add constraint with different names to FK

CREATE TABLE Products (
    id INT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE WarehouseMovements (
    time DATETIME NOT NULL,
    product1 INT NOT NULL,
    product2 INT NOT NULL,
    CONSTRAINT fk_product_1 FOREIGN KEY (product1) REFERENCES Products (id),
    CONSTRAINT fk_product_2 FOREIGN KEY (product2) REFERENCES Products (id)
);