UNIQUE KEY vs KEY

pearl2 · #1 (**permalink**) 03-17-09, 04:40

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

healdem · #2 (**permalink**) 03-17-09, 04:49

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.

r937 · #3 (**permalink**) 03-17-09, 05:39

few occasions????

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

pearl2 · #4 (**permalink**) 03-17-09, 05:56

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)
}

healdem · #5 (**permalink**) 03-17-09, 06:25

no defacto a PRIMARY KEY is a UNIQUE KEY, yo dont need to define duplicate indexes.

healdem · #6 (**permalink**) 03-17-09, 06:30

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.

r937 · #7 (**permalink**) 03-17-09, 06:40

Quote:

Originally Posted by healdem

sorry I don't understand that....

you've never seen tables with auto_increment PKs?????

r937 · #8 (**permalink**) 03-17-09, 06:41

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 )
)

healdem · #9 (**permalink**) 03-17-09, 07:16

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

r937 · #10 (**permalink**) 03-17-09, 07:34

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?

r937 · #11 (**permalink**) 03-17-09, 07:36

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??"

pearl2 · #12 (**permalink**) 03-17-09, 08:50

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.

Quote:

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?

r937 · #13 (**permalink**) 03-17-09, 09:54

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

pearl2 · #14 (**permalink**) 03-17-09, 10:08

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...

r937 · #15 (**permalink**) 03-17-09, 10:24

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

Internet Services

Web Development

Digital Marketing

Consumer Tech