#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,677
    Rep Power
    171

    A design question. Is it better to have id as primary key or composite unique key?


    Hello;

    I decided to use product_AND_usage table in order to relate products and products table together.

    products
    ----------
    id, name

    product_usage
    -------------------
    id, usage

    product_AND_usage
    -------------------
    product_id, usage_id

    Each product can only be of 1 kind, in other words there can never be a dupicate product_id, usage_id combination in
    product_AND_usage table. In order to force this and also improve the performance, which if the following options is the better one?
    Code:
    ALTER TABLE product_and_usage
      ADD UNIQUE index(product_id, usage_id);
    Code:
    ALTER TABLE product_and_usage
      ADD PRIMARY KEY(product_id, usage_id);
    Code:
    ALTER TABLE `product_and_usage`
      ADD `id` INT NOT NULL auto_increment PRIMARY KEY first
    Thank you
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    Originally Posted by zxcvbnm
    which if the following options is the better one?
    the first two

    PK is actually better because it prevents either of the two FKs from being NULL

    the third option, with the id, is useless and inefficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,677
    Rep Power
    171
    Thanks
    Last edited by zxcvbnm; May 4th, 2013 at 05:27 PM.

IMN logo majestic logo threadwatch logo seochat tools logo