Mysql 简明教程

MySQL - Alternate Key

表中的 Alternate Key 只是该表中主键的替代项。换言之，它们是目前未被选作表主键的候选键（但可能成为主键）。因此，它们也可以用于唯一标识表中的元组（或记录）。

An Alternate Key in a table is nothing but an alternative to the primary key in that table. In other words, they are candidate keys that are not currently selected as the primary key of a table (but has a potential to be one). Hence, they can also 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 an ordinary column that is a secondary candidate to be selected as a primary key.

Features of Alternate Keys

尽管备用键不是主键，但它们包含一些自身的重要属性/要素。它们列于下方 −

Even though alternate keys are not primary keys, they contain some important properties/features of their own. They are listed below −

  1. The alternate key does not allow duplicate values.

  2. A table can have more than one alternate keys.

  3. The alternate key can contain NULL values unless the NOT NULL constraint is set explicitly.

  4. All alternate keys can be candidate keys, but all candidate keys can not be alternate keys. As a primary key, which is also a candidate key, can not be considered as an alternate key.

Types of Keys in a Table

下方列出了存在于表中的键 −

Below is the list of keys that are present in a table −

  1. Candidate key

  2. Primary key

  3. Alternate key

  4. Foreign Key

Candidate Key

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

A Candidate key is a subset of super keys that is used to uniquely identify records of a table. It can either be a single field or multiple fields. Primary keys, alternate keys, foreign keys in a table are all types of candidate 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. The syntax is as follows −

CREATE TABLE table_name(
   COLUMN_NAME1 datatype,
   COLUMN_NAME2 datatype,
   ...
   PRIMARY KEY(COLUMN_NAME)
);

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.

没有用于设定数据库表中的备用键的语法。

There is no syntax to set an alternate key in a database table.

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.

在表中设定外键字段的语法如下： −

The syntax to set a foreign key field in a table is −

CREATE TABLE table_name2(
   ... CONSTRAINT constraint_name
   FOREIGN KEY (column_name2)
   REFERENCES table_name1(column_name1)
);

Example

在下例中，我们正在 MySQL 数据库中创建一个名为 CUSTOMERS 的样本表 −

In the following example, we are creating a sample table named CUSTOMERS in the MySQL database −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID)
);

现在，让我们使用 INSERT 语句向此创建的表中插入一些记录，如下所示： −

Now let us insert some records into this table created using the INSERT statement as shown below −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

Verification

为了验证添加到 CUSTOMERS 表中的键，让我们使用以下查询显示表定义 −

To verify the keys added to the CUSTOMERS table, let us display the table definition using the following query −

目前，只有 PRIMARY KEY 设置在表中的 ID 列中。NAME 列像备用键一样，因为它只会像主键列一样只包含唯一记录。而 ID 和 NAME 都是 CUSTOMERS 表中的候选键。

Currently, only PRIMARY KEY is set in the table on ID column. The NAME column acts like the Alternate Key, as it will only contain unique records like a Primary Key column. Whereas, both ID and NAME are the Candidate Keys in the CUSTOMERS table.

Example

为了展示外键用法，我们需要两个表格。以下是对另一个表执行以下查询 ORDERS 与外键设置 CUSTOMER_ID 。

To illustrate the usage of Foreign Key, we would need two tables. Following is the query to create another table ORDERS with the foreign key set as CUSTOMER_ID.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
   CONSTRAINT fk_customers FOREIGN KEY (CUSTOMER_ID)
   REFERENCES CUSTOMERS(ID)
);

使用 INSERT 语句像下面这样向该表中插入值：

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Verification

为了验证 ORDERS 表是否引用 CUSTOMERS 表，我们删除 CUSTOMERS 表而不删除 ORDERS 表。

To verify if the ORDERS table is referenced to the CUSTOMERS table or not, we drop the CUSTOMERS table without dropping the ORDERS table.

DROP TABLE CUSTOMERS;

显示了以下错误 −

Following error is displayed −

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'fk_customers' on table 'orders'.

Rules to be Followed for Alternate Keys

以下是插入记录到表中时应该遵循的备用键规则列表 −

Below are list of rules of alternate keys that should be followed while inserting the record into a table −

  1. Alternate key values should be unique.

  2. Alternate key can not be NULL.