question on index types

[jworisek]

Just curious about index types (haven't found quite what I was looking for in the manual, but maybe Im just searching the wrong key words). What exactly are the differences between Primary, Unique, and Index?

As I understand it:

Primary: only unique entries for specified columns are allowed and null values aren't allowed.

Unique: only unique entries for specified columns are allowed and null values are allowed.

Index: doesn't require uniqueness?

but in joining tables all work in the same sense correct?

[wickning1]

Yes, they all speed up your queries. You can only have one primary key per table (hence "primary"). You can have as many UNIQUEs as you want.

With both PRIMARY and UNIQUE, your INSERTs will fail if you try to insert a row that would break the uniqueness of the indexed column. In exchange, you can be confident that your data is consistent and those columns are never duplicated.

Normal INDEXes just give you the speed boost without demanding uniqueness. You can have as many as you want (although they eat up space).