Hello All,

Thinks its one of the question lots of people ask:

What are the diffrences referring to MySQL
between, uniqe index, primary key and unique constraint ?

what i know is:
-------------------
primary key = in order to retrieve records fast, we use the primary key
for sorting our records.

unique index = also for fast retrieve of data, but not always on the primary key. although having primary key unique, and unique index on another field make it trouble to insertion (duplicate key values).

unique constraint = mainly to restrict duplicate keys using same values.

But, are all of them create an index file, needed to be read each time i retrieve records of data ? are all of them can be found using
show index from tbl ? does anyone knows if the index algorithm is other then
BTree for each one ? assume i use InnoDB, will index be still BTree ?

(Most of my questions are pro - i know, since I looking on MySQL internals).
Thanks
Chanan

Hi again,

assuming all primary key, unique index, and unique constraint
handle index files (as described) for access records

Should i use a primary key on a table ,where most of my operations
are update/insert and delete, while reading is less.

Example, setting a table for system logs, where there are lots of inserts,
update and delete assume i handle system rollbacks. will it be wise to use primary key.

thanks
Chanan

your questions are not really "pro" -- they are very basic

a primary key is a column that is both unique and not null

a unique constraint makes a column unique but it may have nulls

both primary key and unique constraint are implemented via indexes

their purpose is not for fast access, but rather for uniqueness

indexes, in general, are for fast access, and you can have an index on any column

yes, every table should have a primary key

Hi,

As you mentioned the idea of primary key / unique index / unique constraint are to inforce uniqueness on the fields data,
but following "Pro MySQL" (by APress),
index will slow down insert / update and delete operation.

So I wonder if it's a must, creating those to inforce uniquness.

Also, when I did migretion from one DB, to another I was told to remove these constraints, move the data and then inforce them again - this to increase the migretion process. So how will I be able to remove the primary key constraint ?

Also, what about their implementation, does all create an index file which MySQL handle ?

Thanks
Chanan

if you want uniqueness, then it's a must

your question is kind of like "is it true that a VARCHAR(50) column can potentially take up more room than a VARCHAR(10) column?" -- the answer is yes, but that is ~not~ a good reason to store people's surnames in a VARCHAR(10) column

look in da manual, it is under the ALTER TABLE syntax

But it might also make those operations faster