约束条件之主键
primary key 称之为主键
特点
1.单从约束角度上而言主键等价于非空且唯一(not null unique)
mysql> create table t1(id int primary key,name varchar(32));
Query OK, 0 rows affected (0.04 sec)
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> insert into t1 values(1,'nana');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values(1,'aa');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | nana |
+----+------+
1 row in set (0.00 sec)
2.InnoDB存储引擎规定一张表必须有且只有一个主键(主键可以加快数据的查询)
1.如果创建的表中没有主键也没有非空且唯一的字段,那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键
2.如果创建的表没有主键但是有非空且唯一的字段,那么InnoDB存储引擎会自动将该字段设置为主键
eg:
mysql> create table t2(id int not null unique,name varchar(32));
Query OK, 0 rows affected (0.03 sec)
mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> insert into t2 values(1,'nana');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values(1,'aa');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
3.创建表的时候应该有一个‘id’字段(该字段名非固定),并且该字段应该作为主键
4.创建表可以使用单列主键,也可以使用联合主键
mysql> create table t3(id int, nid int,primary key(id,nid));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t3 values(1,2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 values(1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t3 values(1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'
约束条件之自增
auto_increment 称之为自增,该约束条件不能单独使用 必须跟在键后面(主要配合主键一起使用)
mysql> create table t4(id int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table t4(id int primary key auto_increment,name varchar(32));
Query OK, 0 rows affected (0.04 sec)
mysql> desc t4;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
mysql> insert into t4(name) values('nana'),('aa'),('bb'),('cc');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t4;
+----+------+
| id | name |
+----+------+
| 1 | nana |
| 2 | aa |
| 3 | bb |
| 4 | cc |
+----+------+
特点
自增的操作不会因为执行删除数据的操作而回退或者重置
mysql> create table t5(id int primary key auto_increment,name varchar(32));
Query OK