12

I came across the following SQL in a book:

CREATE TABLE 'categories'(
id SMALLINT NOT NULL AUTO INCREMENT,
category VARCHAR(30) NOT NULL,
PRIMARY KEY('id'),
UNIQUE KEY 'category'('category')
)ENGINE=MyISAM DEFAULT CHARSET = utf8;

I was wondering is there a reason why I would need a PRIMARY and UNIQUE KEY in the same table? I guess, underlying that question is, what is the difference between PRIMARY and UNIQUE keys?

locoboy
  • 38,002
  • 70
  • 184
  • 260

5 Answers5

20

The relational model says there's no essential difference between one key and another. That is, when a relation has more than one candidate key, there are no theoretical reasons for declaring that this key is more important than that key. Essentially, that means there's no theoretical reason for identifying one key as a primary key, and all the others as secondary keys. (There might be practical reasons, though.)

Many relations have more than one candidate key. For example, a relation of US states might have data like this.

State      Abbr      Postal Code
--
Alabama    Ala.      AL
Alaska     Alaska    AK
Arizona    Ariz.     AZ
...
Wyoming    Wyo.      WY

It's clear that values in each of those three columns are unique--there are three candidate keys.

If you were going to build a table in SQL to store those values, you might do it like this.

CREATE TABLE states (
  state varchar(15) primary key,
  abbr varchar(10) not null unique,
  postal_code char(2) not null unique
);

And you'd do something like that because SQL doesn't have any other way to say "My table has three separate candidate keys."

I didn't have any particular reason for choosing "state" as the primary key. I could have just as easily chosen "abbr" or "postal_code". Any of those three columns can be used as the target for a foreign key reference, too.

And as far as that goes, I could have built the table like this, too.

CREATE TABLE states (
  state varchar(15) not null unique,
  abbr varchar(10) not null unique,
  postal_code char(2) not null unique
);
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • 1
    In fact, most if not all database products will let you create relations off unique constraints. Thus, in theory, you could have used a unique constraint on all three. Technically, ISO could have used the words "unique key" instead of just "unique" but that would have been redundant. – Thomas Jun 17 '11 at 00:20
  • Thanks. This is helpful. So what does it mean when the book says `UNIQUE KEY` – locoboy Jun 17 '11 at 00:22
  • @cfarm54: It means the column named "category" must contain unique values. – Mike Sherrill 'Cat Recall' Jun 17 '11 at 00:29
  • 1
    @cfarm54- "UNIQUE KEY" (aside from being redundant) is a unique constraint. I.e., it is a constraint like a Primary Key constraint which is forcing all non-null values in your category column to be unique. If someone tries to insert a duplicate, they will get an error and the insert will be rejected. – Thomas Jun 17 '11 at 00:30
  • Might be worthwhile to post a few of the practical reasons that a primary key is different, such as disk ordering, for completeness' sake. Good answer, though. –  Jun 17 '11 at 00:37
  • @Thomas: You have good points, just one note. To be precise, it's forcing `null` values to be unique too, i.e. there can be only one `null` value in "unique" column (in Oracle) – Alexander Malakhov Jun 30 '11 at 02:41
  • @Alexander Malakhov - That actually depends on the product. IIRC, MySQL ignores nulls on Unique constraints which means there can be multiple null values. SQL Server treats all nulls a single value which means if a Unique constraint is on a single column, there can only be a single null value. – Thomas Jun 30 '11 at 03:29
  • @Thomas: It turns out you are right. From [official manual](http://dev.mysql.com/doc/refman/5.6/en/create-index.html): `For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL` – Alexander Malakhov Jun 30 '11 at 06:57
  • Minor point: when you say "relation" you actually mean "relation variable" or relvar for short. – onedaywhen Nov 10 '11 at 12:03
8

I'm surprised that nobody mentionned that a primary key can be referenced as foreign key into other tables.

Also an unique constraint allows NULL values.

Luc M
  • 16,630
  • 26
  • 74
  • 89
  • 3
    Most products will let you use a Unique constraint in FK reference. I.e., you are not generally required to reference a PK constraint even though that is the most common. – Thomas Jun 17 '11 at 01:28
4

Similarity

Both a PRIMARY and UNIQUE index create a constraint that requires all values to be distinct (1).

Difference

The PRIMARY key (implicitly) defines all key columns as NOT NULL; additionally, a table can only have one primary key.

(1) Each NULL value is considered to be distinct.

Ja͢ck
  • 170,779
  • 38
  • 263
  • 309
4

The reason you need two uniqueness restrictions (one being the Primary Key) is that you are using Id as a surrogate key. I.e., it is an arbitrary value that has no meaning in relation to the data itself. Without the unique key (or colloquially known as "business key" i.e, a key that the user would recognize as being enforced), a user could add two identical category values with different arbitrary Id values. Since users should never see the surrogate key, they would not know why they are seeing a duplicate even though the database would think they are different.

When using surrogate keys, having another unique constraint on something other than the surrogate key is critical to avoid duplicate data.

Depending on who you talk to and how they read the specification, Unique keys( which is redundant by the way. A "key" is by definition unique) are also not supposed to allow nulls. However, one can also read the specifications as saying that Unique constraints, unlike Primary Key constraints, are in fact supposed to allow nulls (how many nulls are allowed also varies by vendor). Most products, including MySQL, do allow nulls in Unique constraints whereas Primary Key constraints do not.

Thomas
  • 63,911
  • 12
  • 95
  • 141
1

A UNIQUE constraint and PRIMARY key both are similar and it provide unique enforce uniqueness of the column on which they are defined. Some are basic differences between Primary Key and Unique key are as follows.

Primary key

Primary key cannot have a NULL value. Each table can have only single primary key. Primary key is implemented as indexes on the table. By default this index is clustered index. Primary key can be related with another table's as a Foreign Key. We can generate ID automatically with the help of Auto Increment field. Primary key supports Auto Increment value.

Unique Constraint

Unique Constraint may have a NULL value. Each table can have more than one Unique Constraint. Unique Constraint is also implemented as indexes on the table. By default this index is Non-clustered index. Unique Constraint cannot be related with another table's as a Foreign Key. Unique Constraint doesn't support Auto Increment value.

You can find detailed information from: http://www.oracleinformation.com/2014/04/difference-between-primary-key-and-unique-key.html

Brahmareddy K
  • 564
  • 8
  • 9