Sql 简明教程

SQL - Alternate Key

The SQL Alternate Key

数据库表中的 SQL Alternate Keys 是目前未选作主键的候选键。它们可用于唯一识别表中的元组（或记录）。

SQL Alternate Keys in a database table are candidate keys that are not currently selected as a primary key. They can be used to uniquely identify a tuple(or a record) in a table.

没有特定的查询或语法用于在表中设置备用键。它只是一个接近的二级候选键，可以选作主键。因此，它们也称为次要候选键。

There is no specific query or syntax to set the alternate key in a table. It is just a column that is a close second candidate which could be selected as a primary key. Hence, they are also called secondary candidate keys.

让我们通过一个例子理解备用键的概念。假设我们有一个名为 CUSTOMERS 的表，其中包含 ID、NAME、AGE、AADHAAR_ID、MOBILE_NO 和 SALARY 等不同字段，如下所示：

Let us understand the concept of alternate key with an example. Suppose we have a table named CUSTOMERS with various fields like ID, NAME, AGE, AADHAAR_ID, MOBILE_NO and SALARY as shown below.

像客户的 id、手机号码和 aadhaar 号码之类的详细信息是唯一的，我们可以使用其各自的字段（ID、AADHAAR_ID 和 MOBILE_NO）唯一识别 CUSTOMERS 表中的记录。因此，这三个字段可以被视为备选键。

The details like id, mobile number and aadhaar number of a customer are unique, and we can identify the records from the CUSTOMERS table uniquely using their respective fields; ID, AADHAAR_ID and MOBILE_NO. Therefore, these three fields can be treated as candidate keys.

在这些字段中，如果有一个被声明为 CUSTOMERS 表的主键，那么剩下的两个将是备用键。

And among them, if one is declared as the primary key of the CUSTOMERS table then the remaining two would be alternate keys.

Features of Alternate Keys

以下是备用键的一些重要性质/特征 −

Following are some important properties/features of alternate keys −

The alternate key does not allow duplicate values.
A table can have more than one alternate keys.
The alternate key can contain NULL values unless the NOT NULL constraint is set explicitly.
All alternate keys can be candidate keys, but all candidate keys can not be alternate keys.
The primary key, which is also a candidate key, can not be considered as an alternate key.

Example

为了更好地理解，让我们创建上述讨论的表，演示各种键的使用并说明可被视为备用键的字段。

For a better understanding, let us create the above discussed table demonstrating the usage of the various keys and illustrating the fields that can be considered as alternate keys.

CREATE TABLE CUSTOMERS(
   ID INT,
   NAME VARCHAR (20),
   AGE INT,
   AADHAAR_ID BIGINT,
   MOBILE_NO BIGINT,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID)
);

现在，使用 INSERT 语句向 CUSTOMERS 表中插入一些记录，如下所示 −

Now, insert some records into the CUSTOMERS table using the INSERT statement as shown below −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 90123498456, 9023456789, 22000.00 ),
(2, 'Khilan', 25, 91123249545, 9032456578, 24500.34 ),
(3, 'Kaushik', 23, 91223242546, 9012436789, 20000.12 );

该表将被创建为：

The table will be created as −

ID	NAME	AGE	AADHAAR_ID	MOBILE_NO	SALARY
1	Ramesh	32	90123498456	9023456789	22000.00
2	Khilan	25	91123249545	9032456578	24500.34
3	Kaushik	23	91223242546	9012436789	20000.12

Keys in a table

总结一下，让我们重新审视一下数据库表中的所有键 −

As a summary lets revisit all the keys in a database table −

Candidate Key

Candidate key 是超级键的子集，用于唯一标识表中的记录。它可以是单个字段或多个字段。表中的主键、备用键和外键都是候选键的类型。

A Candidate key is a subset of super keys that is used to uniquely identify records of a table. It can be a single field or multiple fields. The primary keys, alternate keys, foreign keys in a table are all types of candidate key.

Primary Key

Primary Key 是用于从表中检索记录的主键。它是一个表中的单个列或字段，可唯一标识数据库表中的每条记录。

A Primary Key is a main key that is used to retrieve records from a table. It is a single column or field in a table that uniquely identifies each record in a database table.

在使用 CREATE TABLE 语句创建表时可以使用 PRIMARY KEY 关键字进行设置。以下是针对表中的列创建主键约束的基本语法 −

It can be set using the PRIMARY KEY keyword while creating a table using the CREATE TABLE statement. Following is the basic syntax to create primary key constraint on a column in a table −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY(column_name)
);

Foreign Key

一个表的主键是另一个表中的 Foreign key 。在向这些表中插入值时，主键字段中的值必须与外键字段中的值匹配；否则，外键列不会接受 INSERT 查询并抛出错误。

The Primary key of one table will be the Foreign key in another table. While inserting values into these tables, values in the primary key field must match the values in the foreign key field; otherwise, the foreign key column will not accept the INSERT query and throws an error.

在 SQL 服务器中，在表中设置外键字段的语法是 −

In SQL server, the syntax to set a foreign key field in a table is −

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT fk_name
	FOREIGN KEY (column_name)
	REFERENCES referenced_table(referenced_column)
);

Alternate Key

Alternate key 是可以作为主键但实际上不是主键的候选键。与主键相同，它还可以唯一标识表中的字段中的记录，以便从所述表中检索行元组。在一个表中，可以有一个或多个字段标识为备用键。

An Alternate key is a candidate key that could be a primary key but is not. Like primary key, it also uniquely identifies the records in a field of a table to retrieve row tuples from the said table. There can be a single or multiple fields identifying as alternate keys in a table.