General Questions about MySQL

Mao Chen asked:

I got a general question regarding key and index in MySQL. Are they essentially same thing? If not, what's the difference?

Brad Bridge (Database Design Instructor UCSD Extension) answered:

KEYS have NOTHING to do with INDEXES. INDEXES are to improve the performance of SELECT (read) operations, whereas KEYS are for Data Integrity. The Primary Key enforces Entity Integrity or Entity Uniqueness by preventing duplicate Primary Key column values. The Foreign Key on the other hand enforces Referential Integrity by preventing deletion of rows in the parent table when there are associated rows in the child table. The Foriegn Key also prevents illegitimate rows from being entered in the child table when there is no associated parent row in the parent table.