约束条件之主键、外键、表关系之一对一、一对多、多对多

约束条件之主键

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