Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    27

    Unanswered: unique index, primary key and constraints

    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

  2. #2
    Join Date
    Dec 2009
    Posts
    27

    also

    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2009
    Posts
    27

    more questions

    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bchanan View Post
    So I wonder if it's a must, creating those to inforce uniquness.
    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

    Quote Originally Posted by bchanan View Post
    So how will I be able to remove the primary key constraint ?
    look in da manual, it is under the ALTER TABLE syntax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by bchanan View Post
    index will slow down insert / update and delete operation.
    But it might also make those operations faster

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •