InnoDB foreign key and index duplication

foreign keyindexinnodb

I'm have a part table like this:

    part_id            INT
    manufacturer_id    INT
    supplier_id        INT
    part_number        VARCHAR(50)
    part_description   VARCHAR(120)

with foreign keys:

    CONSTRAINT fk_supplier_to_part FOREIGN KEY supplier_id REFERENCES supplier.supplier_id
    CONSTRAINT fk_manufacturer_to_part FOREIGN KEY manufacturer_id REFERENCES manufacturer.manufacturer_id

and indexes: 

    KEY ix_supplier_id (part.supplier_id)
    KEY ix_manufacturer_id (part.manufacturer_id)
    KEY fk_supplier_to_part (part.supplier_id)
    KEY fk_manufacturer_to_part (part.manufacturer_id)

My question is, are the ix_supplier_id and ix_manufacturer_id indexes required? I know that a foreign key needs a 'matching index' but does the index need to have the same name as well as matching columns?

Best Answer

ix_supplier_id and ix_manufacturer_id are not required; as long as you have the fields intended for foreign keys indexed by themselves (as in: (part.supplier_id)) , the names of the keys are arbitrary. You can also have them in composite indexes, but there must be at least one index of the whole field and nothing but the field. So help me DDL =).

Related Question