mysql 约束条件 primary key 主键

 

 

 

primary key字段的值不为空且唯一

约束：not null unique

存储引擎:innodb

对于innodb来说，一张表内必须有一个主键

 

 

单列做主键
多列做主键（复合主键）

 

通常都是id字段 设置主键

单列主键

mysql> create table t17(id int primary key,name char(16));
Query OK, 0 rows affected (0.01 sec)

mysql> desc t17;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(16) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

验证  重复了

primary 相当于 unique

mysql> insert into t17 values(1,'mike'),(2,'jack');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t17;
+----+------+
| id | name |
+----+------+
|  1 | mike |
|  2 | jack |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into t17 values(2,'ben');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

 

 

只传name字段，没有传id

默认有个 default id 设置0 不为空

mysql> insert into t17(name) values('ben');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t17;
+----+------+
| id | name |
+----+------+
|  0 | ben  |
|  1 | mike |
|  2 | jack |
+----+------+
3 rows in set (0.00 sec)

 

再插入一次 id 0 重复了

mysql> insert into t17(name) values('tom');
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY

 

总结：如果不设置主键 ，mysql的存储引擎又是innodb ，mysql会找一个字段设置为主键

会找一个不为空not null 且唯一的字段 unique 设置为主键 primary key，

如果都没有找到，整张表扫描完以后，所有字段都找不到一个符合条件的字段， 会找一个字段 设置一个隐藏的主键 

mysql是innodb，建立一张表，应该自己建立一个主键。

 

 

创建一张表 id字段 设置not null unique，不设置主键

为主键了id

mysql> create table t18(id int not null unique,name varchar(16));
Query OK, 0 rows affected (0.01 sec)

mysql> desc t18;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

在所有字段后单独定义primary key

mysql> create table department3(id int,name varchar(16),primary key(name));
Query OK, 0 rows affected (0.01 sec)

mysql> desc department3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | NO   | PRI |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

 

mysql> create table department4(id int,name varchar(16),constraint primary key(name));
Query OK, 0 rows affected (0.01 sec)

mysql> desc department4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | NO   | PRI |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

==================多列做主键================
复合主键，就是多个字段联合唯一
primary key(ip,port)

mysql> create table t19(ip varchar(16),port char(16),primary key(ip,port));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t19;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ip    | varchar(16) | NO   | PRI |         |       |
| port  | char(16)    | NO   | PRI |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

要求ip相同 端口不同

mysql> insert into t19 values('192.168.1.1',80),('192.168.1.1',81);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t19;
+-------------+------+
| ip          | port |
+-------------+------+
| 192.168.1.1 | 80   |
| 192.168.1.1 | 81   |
+-------------+------+
2 rows in set (0.00 sec)

mysql> insert into t19 values('192.168.1.1',81);
ERROR 1062 (23000): Duplicate entry '192.168.1.1-81' for key 'PRIMARY'