唯一键和主键之间的区别 [英] Difference between Unique Key and Primary Keys

问题描述

我在一本书中遇到以下SQL:

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;

我想知道为什么在同一张表中需要一个PRIMARY和UNIQUE KEY吗?我想，这个问题的根源在于，PRIMARY和UNIQUE键之间有什么区别?

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?

推荐答案

关系模型说，一个键和另一个键之间没有本质区别.也就是说，当一个关系具有多个候选键时，没有理论上的理由声明该这个键比那个键更重要.从本质上讲，这意味着没有理论上的理由将一个键识别为主键，将所有其他键识别为辅助键. (不过，可能有实际原因.)

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.

如果要在SQL中构建表来存储这些值，则可以这样操作.

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

您将执行类似的操作，因为SQL没有其他方法可以说我的表具有三个单独的候选键."

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

我没有选择"state"作为主键的任何特殊原因.我可以轻松选择"abbr"或"postal_code".这三列中的任何一列也可以用作外键引用的目标.

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

这篇关于唯一键和主键之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！