Sql 简明教程

SQL - Primary Key

The SQL Primary Key

SQL Primary Key 是一个列（或组合列），可以唯一识别一个数据库表格中的每条记录。主键也能加速数据访问，并且被用来建立表格之间的关系。

The SQL Primary Key is a column (or combination of columns) that uniquely identifies each record in a database table. The Primary Key also speeds up data access and is used to establish a relationship between tables.

让我们想象一下，你正在开发一个叫做“客户管理系统”的应用程序来处理一个仅限会员进入度假胜地的所有客户数据。这些数据可能包括他们的个人信息、分配的会员 ID、他们选择的会员的其他详细信息等。在这个数据库中创建的所有表格，都会使用会员 ID 来区分不同的客户。所以，这个字段将是主键。

Let us say, you are developing an application called "Customer Management System" to handle all the customer data of a member-only resort. This data can include their personal details, assigned member IDs, other details of the membership they opted, etc. And in all the tables created within this database, the member ID is used to distinguish the customers from each other. So, this field will be the Primary Key.

下面是 CUSTOMERS 表的图表，这个表格保存了客户的个人详细信息。我们可以看到，主键被定义在 CUST_ID 列。使用这个主键，我们可以找到任何一个客户的记录。

Following is the diagram of a CUSTOMERS table that holds the personal details of the customers. And as we can observe, the primary key is defined on the CUST_ID column. Using this primary key, we can retrieve a unique record of any customer.

Points to Remember

以下是 PRIMARY KEY 的几个要点：

Here are some key points of the PRIMARY KEY −

It contains only a unique value.
It can not be null.
One table can have only one Primary Key.
A primary key length cannot be more than 900 bytes.

Creating an SQL Primary Key

在使用 CREATE TABLE statement 创建表格时，你可以通过对列名称指定一个 “PRIMARY KEY” 的关键词，向表格的特定列添加主键约束。

While creating a table using the CREATE TABLE statement, you can add the primary key constraint on a particular column of the table just by to specifying the name of the column along with the keyword "PRIMARY KEY".

Syntax

以下是将表格的列定义为主键的语法：

Following is the syntax to define a column of a table as a primary key −

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

Example

在以下示例中，我们正尝试在 SQL 数据库中创建一个叫做 CUSTOMERS 的表格，这个表格有各个字段。在创建表格时，我们将在叫做 ID 的列上添加 “PRIMARY KEY” 约束。

In the following example, we are trying to create a table with the name CUSTOMERS with various fields in an SQL database. While creating the table, we will add the constraint "PRIMARY KEY" on the column named ID.

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

Output

以下是上述 SQL 语句的输出：

Following is the output of the above SQL statement −

Query OK, 0 rows affected (0.03 sec)

Verification

众所周知，主键的值必须是唯一的，所以你不能插入有相同 ID 的记录。在这里，我们将验证对 ID 列创建的约束，通过插入有重复 ID 值的记录。

As we know primary key value must be unique, so you can not insert the record with the same ID. Here, we will verify the constraint created on the ID column, by inserting records with duplicate ID values.

首先，让我们向 CUSTOMERS 表格中插入一条记录：

First of all, let’s insert a record into the CUSTOMERS table −

INSERT INTO CUSTOMERS VALUES
(3, 'Kaushik', 23, 'Kota', 2000.00);

现在，让我们插入另一条同样的 ID 记录：

Now, let’s insert one more record with same ID −

INSERT INTO CUSTOMERS VALUES
(3, 'Chaitali', 25, 'Mumbai', 6500.00);

正如我们在上面提到的，如果一个数据库表格中的某个字段/列被定义为主键，那么两条记录不能在这个列/字段中有相同的值。因此，第二个插入语句会产生以下错误：

As we have mentioned above, if any field/column is defined as Primary Key in a database table, two records can not have the same value in that column/field. Therefore, the second insert statement generates the following error −

ERROR 1062 (23000): Duplicate entry '3' for key 'customers.PRIMARY'

类似地，一个主键列不能包含 NULL 值。在这里，我们使用 INSERT 语句向主键列 (ID) 传递了一个 NULL 值。

Similarly, a primary key column cannot contain null values. Here, using the INSERT statement we are passing a NULL value to the primary key column (ID).

INSERT INTO CUSTOMERS VALUES
(NULL, 'Komal', 22, 'Hyderabad', 4500.00);

这个语句会产生以下错误：

This statement generates the following error −

ERROR 1048 (23000): Column 'ID' cannot be null

Creating Primary Key on an Existing Column

我们也可以使用 ALTER TABLE 语句在表的一个现有列中添加 PRIMARY KEY 约束。

We can also add the PRIMARY KEY constraint on an existing column of a table using the ALTER TABLE statement.

Syntax

以下是在表的现有列中创建主键约束的语法 −

Following is the syntax to create a primary constraint on existing columns of a table −

ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY (column_name);

Example

在此示例中，我们正在向现有 CUSTOMERS 表的 NAME 列添加 PRIMARY KEY 约束 −

In this example, we are adding the PRIMARY KEY constraint on the NAME column of the existing CUSTOMERS table −

ALTER TABLE CUSTOMERS ADD CONSTRAINT PRIMARY KEY(NAME);

Output

以下是上述语句的输出 −

Following is the output of the above statement −

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Dropping an SQL Primary Key

如果可以向表中列添加主键约束，你也可以删除它。使用 ALTER TABLE… DROP 语句来执行此操作。

If you can add a Primary Key Constraint to a column in the table, you can drop it as well. This is done by using the ALTER TABLE… DROP statement.

Syntax

以下是 ALTER TABLE 语句的语法，可用于从表的列中删除主键约束 −

Following is the syntax of the ALTER TABLE statement to can drop the Primary key constraints from the column of a table −

ALTER TABLE table_name DROP PRIMARY KEY;

Example

让我们考虑 CUSTOMERS 表，我们在其中一个名为 ID 的列上创建了主键约束。通过执行以下语句，可以从 ID 列删除此约束 −

Let us consider the CUSTOMERS table where we have created a primary key constraint on a column named ID. You can drop this constraint from the column ID by executing the following statement −

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Output

上述 SQL 查询生成以下输出 −

The above SQL query produces the following output −

Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

Verification

由于我们已从名为 ID 的列中删除了主键，因此可以插入具有相同 ID 的多个记录。以下语句插入了四个具有相同 ID 的记录 −

As we have dropped the Primary key from the column named ID, we can insert multiple records with the same ID. Following statement inserts four records with the same ID −

INSERT INTO CUSTOMERS VALUES
(3, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(3, 'Hardik', 27, 'Bhopal', 8500.00 ),
(3, 'Komal', 22, 'Hyderabad', 4500.00 ),
(3, 'Muffy', 24, 'Indore', 10000.00 );

如果验证此表的内容，你将发现多个具有相同 ID 的记录 −

If you verify the content of this table, you can find multiple records with same ID −

SELECT * FROM CUSTOMERS;

将显示以下形式的表 −

The table will be displayed as −

ID	NAME	AGE	ADDRESS	SALARY
3	Kaushik	23	Kota	2000.00
3	Chaitali	25	Mumbai	6500.00
3	Hardik	27	Bhopal	8500.00
3	Komal	22	Hyderabad	4500.00
3	Muffy	24	Indore	10000.00