Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: UNIQUE KEY vs KEY

    Hi everyone,

    What is difference between declaring a column with UNIQUE KEY and one with just KEY?

    I thought I knew the difference the last time but now I'm not exactly sure.

    Thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the clue is in the names....
    a UNIQUE KEY means there cannot be another row with the same values in the index, whereas a KEY doesn't care or worry about duplicates.

    you would use a unique key if there cannot be a duplicate value, intrinsically its not very different to a PRIMARY KEY (PK) a PK is also a UNIQUE KEY, but the PK has special meaning inside the db, that is the key used to enforce relational integrity, and should be the foreign key in 'child' tables. in the child table the FOREIGN KEY is usually just a KEY as its reasonable to expect there to be duplicates.

    there are few occasions where a UNIQUE KEY would be specified in addition to a PRIMARY KEY. off the top of my head the only one I can think of right now is if you have a system which controls its own data, but is also reliant on data from other systems, you may define a UNIQUE KEY constraint to enforce that there are no duplicate 'other system' references.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    few occasions????

    there should be a UNIQUE KEY in every single table that uses an auto_increment PK

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks to both of you

    So if I've a column that's defined as a Primary Key, I should also define that column as a Unique Key? For example:

    Code:
    CREATE TABLE users (
        userid MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        /* other stuff here */
        UNIQUE KEY (userid)
    }

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no defacto a PRIMARY KEY is a UNIQUE KEY, yo dont need to define duplicate indexes.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    few occasions????

    there should be a UNIQUE KEY in every single table that uses an auto_increment PK

    sorry I don't understand that....
    I'd agree that just about every table in a relational DB should have a primary key, which is defacto a UNIQUE KEY, but Ive come across very few occasions where I'd have a PRIMARY KEY and a UNIQUE KEY both referencing different columns.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    sorry I don't understand that....
    you've never seen tables with auto_increment PKs?????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    So if I've a column that's defined as a Primary Key, I should also define that column as a Unique Key?
    no!!

    what you want to do is this --
    Code:
    CREATE TABLE users 
    ( userid MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    , summat /* other stuff here */
    , UNIQUE KEY ( summat )
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    you've never seen tables with auto_increment PKs?????
    NO
    I've very rarely come across a table with both a Primary Key AND a Unique Key, and when I have, usually its bad design where they were referencing the same column(s). Its pretty rare to find a Primary Key and a Unique Key referencing different columns. I've seen it where a candidate key was a complex composite key, and a autonumber was used in place. but its relatively rare, in my limited experience, to find more than one unique index in a table that references different columns
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    healdem, i have a sneaky feeling you are pulling an elaborate april fool's joke on me, hoping to string it out for a couple of weeks until it's actually april 1

    what's wrong with this table?
    Code:
    CREATE TABLE databases
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , name VARCHAR(37)
    );
    can't spot the error?

    okay, see if you can spot the error here --

    105 DB2
    109 Adabas
    113 Oracle
    117 MySQL
    121 Access
    127 Firebird
    135 Informix
    142 Ingres
    156 SQL Server
    178 Firebird
    187 MySQL
    199 PostgreSQL

    still can't spot the error?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    Its pretty rare to find a Primary Key and a Unique Key referencing different columns.
    it's actually quite common, if the database designers are using an auto_increment as the PK and they know what they're doing

    it's only developers who don't understand keys at all who create an auto_increment PK and then forget to declare the necessary UNIQUE KEY on the "real" key of the table

    what's one of the most common questions posted on database forums?

    "HALP!! how do i removes all duplicates except one from my table??"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by r937
    no!!

    what you want to do is this --
    Code:
    CREATE TABLE users 
    ( userid MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    , summat /* other stuff here */
    , UNIQUE KEY ( summat )
    )
    Hm...I'm a bit lost here.

    there should be a UNIQUE KEY in every single table that uses an auto_increment PK
    So every table that has an auto_increment PK should have a UNIQUE KEY - did I rephrase it correctly? What's the purpose of this UNIQUE KEY?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    What's the purpose of this UNIQUE KEY?
    to ensure that there are no duplicates

    see post #10 in this thread

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Ah thanks! I missed the double entry in your message #10.

    But what if duplicates are allowed in a table? For instance, in a private message system, two or more people writing the same subject title...

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    But what if duplicates are allowed in a table? For instance, in a private message system, two or more people writing the same subject title...
    there are also many other columns that are allowed to duplicate

    for example, two messages by the same person

    we need to step back and ask ourselves exactly what constitutes a duplication of a message (it would be something like: same sender, same title, same recipient, same datetime sent, etc.)

    you picked an example where duplicates would be extremely difficult to produce during the normal operation of the application

    in those cases, yes, it would be unusual to declare a UNIQUE KEY, which would have to be a composite key consisting of many columns -- there'd be little point

    nevertheless, the number of tables i've seen with an auto_increment PK that ~should~ have a UNIQUE KEY but do not is frighteningly large
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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