Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 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 =).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.