MySQL -- 主键约束

MySQL – 主键约束

---

　　主键，是一个表中一列或者多列的组合，规定主键不能重复，也不能为空值，且每个表只能有一个主键，主键是唯一性索引的一种，但二者并不等同。

在MySQL的官方说明中，MySQL是这样处理主键约束的：

Normally, errors occur for data-change statements (such as INSERT or UPDATE) that would violate primary-key, unique-key, or foreign-key constraints. If you are using a transactional storage engine such as InnoDB, MySQL automatically rolls back the statement. If you are using a nontransactional storage engine, MySQL stops processing the statement at the row for which the error occurred and leaves any remaining rows unprocessed.

MySQL supports an IGNORE keyword for INSERT, UPDATE, and so forth. If you use it, MySQL ignores primary-key or unique-key violations and continues processing with the next row. See the section for the statement that you are using (Section 13.2.5, “INSERT Syntax”, Section 13.2.11, “UPDATE Syntax”, and so forth).

You can get information about the number of rows actually inserted or updated with the
mysql_info() C API function. You can also use the SHOW WARNINGS statement. See Section 27.8.7.36, “mysql_info()”, and Section 13.7.5.40, “SHOW WARNINGS Syntax”.

InnoDB and NDB tables support foreign keys. See Section 1.8.3.2, “FOREIGN KEY Constraints”.

如果使用的是事务型储存引擎，当遇到违反主键约束的操作被执行时将会自动进行事务回滚。

使用主键约束

在建表的时候，常用下列两种方式：

mysql> CREATE TABLE tb_test (
    ->     id INT PRIMARY KEY);
Query OK, 0 rows affected (0.05 sec)

或

mysql> CREATE TABLE tb_test (
    ->     id INT,
    ->     PRIMARY KEY(`id`));
Query OK, 0 rows affected (0.03 sec)

结果：

mysql> DESC tb_test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

还可以使用多字段联合主键：

mysql> CREATE TABLE tb_test(
    ->     id INT,
    ->     address VARCHAR(20),
    ->     PRIMARY KEY(`id`, `address`));
Query OK, 0 rows affected (0.03 sec)

mysql> DESC tb_test;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| address | varchar(20) | NO   | PRI | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除主键：

mysql> ALTER TABLE tb_test
    -> DROP PRIMARY KEY;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb_test;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| address | varchar(20) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

新增主键:

mysql> ALTER TABLE tb_test
    -> ADD PRIMARY KEY(`id`);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb_test;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| address | varchar(20) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)