primary key字段的值不为空且唯一
约束：not null unique
存储引擎:innodb
对于innodb来说，但一个表内只能有一个主键primary key
单列做主键
多列做主键（复合主键）
通常都是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，
如果都没有 会找一个字段 设置一个隐藏的主键
创建一张表 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‘
原文：https://www.cnblogs.com/mingerlcm/p/9812074.html