Unique limitation in MySQL

Question

Is a unique constraint a default index? If not, does the unique constraint have the same performance results as the indexed column when used in a SELECT ... WHERE ?

thanks

Answer 1

A unique constraint is necessarily an index. Usually you define it as "UNIQUE INDEX". In any case, you will need to index in order to effectively implement the unique constraint, so having one of them is not a disadvantage.

Answer 2

The limitation is actually very different from the index: it just says that MySQL should provide uniqueness for you. However, the index (although it may be unique) refers to physical ordering on your hard drive or additional structures (usually this tree), which allows you to efficiently search in a column.

However, you can confuse all this with primary keys that define a unique (usually clustered) unique index.

Answer 3

The only restriction is a way of expressing the fact that something (for example, some combination of attribute values) must be unique within the whole relationship ("table")).

This is at the level of logical design.

The index is perhaps a useful way to ensure this restriction.

This is at the level of PHYSICAL design.

Some DBMS products can infer certain physical constructs, such as having a certain index, from having certain logical constructs, such as a UNIQUE constraint. Others cannot.

Answer 4

UNIQUE is actually a restriction on the index, so yes, UNIQUE implies that there is an index in the field in which you are unique.

Answer 5

Check the primary key constraint before using unique constraints.

The primary key constraint comes down to declaring a unique constraint and a nonzero constraint. If the primary key contains more than one column, each column receives a nonzero constraint, but the only constraint applies to all columns taken together.

When you declare a primary key, the DBMS will create an index for you. You can opt out of the index if you want, but you will get terrible performance when the DBMS scans tables to verify uniqueness.

Primary key constraints provide entity integrity, while LINK constraints (foreign key) provide referential integrity. Together they have a long way to ensure data integrity.