键(key)的值:
键是PRI,则列是主键或多列主键中的列之一。【主键约束】
键是UNI,则该列是唯一索引的第一列。(唯一索引允许多个空值,但可以通过检查Null字段来判断该列是否允许空。) 【唯一约束】
键是MUL,则该列是非唯一索引的第一列,其中允许在列中多次出现给定值。【可以重复】
主键(primary key)的作用以及企业用途
主键介绍:
绝对唯一性,存在性,不可重复性,用来区分数据的。(单个表只能有一个主键,但一个主键可以对应多个字段)
主键一般用来关联表,建立表与表之间的关系。
主键不能设置为NULL值(除非字段还设置为了自增等属性)。
mysql> create table t_8(
-> id int(20) primary key, name varchar(30));
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(20) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
添加主键:
· alter table /表名/ add primary key(/字段/,/字段/...); (可以通过修改字段来添加主键。)
mysql> create table t_8(
-> id int(20), name varchar(30));
Query OK, 0 rows affected (0.01 sec)
mysql> desc t_8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(20) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table t_8 add primary key(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(20) | YES | | NULL | |
| name | varchar(30) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
删除主键:
· alter table /表名/ drop primary key;
mysql> desc t_8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(20) | YES | | NULL | |
| name | varchar(30) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table t_8 drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(20) | YES | | NULL | |
| name | varchar(30) | NO | | NULL | |