一、概述

为了约束用户对数据增，删，改，以确保数据正确，有效，合规。

有以下几种约束

not null 非空指定某列不能为空
unique 唯一指定某列或某几列的组合不能重复
primary key 主键指定某列的值可以唯一标识该列记录
foreign key 外键指定该行记录从属于主表的某条记录，主要用于参照完整性

二、not null

null 某列可以为空
not null 某列不可以为空

2.1 实例1

mysql> create table t2(id int not null) engine=innodb default charset = utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> desc t2;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id    | int(11) | NO   |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)

mysql> insert into t2(id) values(2);

Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;

+----+

| id |

+----+

|  2 |

+----+

1 row in set (0.00 sec)

# 插入空值会报错

mysql> insert into t2(id) values(null);

ERROR 1048 (23000): Column 'id' cannot be null

2.2 与default 混用

mysql> create table t3(id1 int not null,id2 int not null default 10)engine = innodb default charset = utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> desc t3;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id1   | int(11) | NO   |     | NULL    |       |

| id2   | int(11) | NO   |     | 10      |       |

+-------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)

# 如果不指定默认列值，则使用默认值

mysql> insert into t3(id1) values(1);

Query OK, 1 row affected (0.01 sec)

mysql> select * from t3;

+-----+-----+

| id1 | id2 |

+-----+-----+

|   1 |  10 |

+-----+-----+

1 row in set (0.00 sec)

# 向默认值列插入数据，新数据会覆盖默认数据

mysql> insert into t3(id1,id2) values(12,12);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;

+-----+-----+

| id1 | id2 |

+-----+-----+

|   1 |  10 |

|  12 |  12 |

+-----+-----+

三、unique

指定某列或某几列不能重复

3.1 创建唯一索引的两种方式

# 方法1

mysql> create table userinfo1(

    -> id int auto_increment primary key,

    -> name varchar(20) not null unique,

    -> password varchar(100) not null)

    -> engine = innodb default charset = utf8;

Query OK, 0 rows affected (0.01 sec)

mysql> desc userinfo1

    -> ;

+----------+--------------+------+-----+---------+----------------+

| Field    | Type         | Null | Key | Default | Extra          |

+----------+--------------+------+-----+---------+----------------+

| id       | int(11)      | NO   | PRI | NULL    | auto_increment |

| name     | varchar(20)  | NO   | UNI | NULL    |                |

| password | varchar(100) | NO   |     | NULL    |                |

方法2：

mysql> create table userinfo2(

    -> id int auto_increment primary key,

    -> name varchar(20),

    -> password varchar(100),

    -> unique(name))

    -> engine = innodb default charset = utf8;

Query OK, 0 rows affected (0.01 sec)

mysql> desc userinfo2;

+----------+--------------+------+-----+---------+----------------+

| Field    | Type         | Null | Key | Default | Extra          |

+----------+--------------+------+-----+---------+----------------+

| id       | int(11)      | NO   | PRI | NULL    | auto_increment |

| name     | varchar(20)  | YES  | UNI | NULL    |                |

| password | varchar(100) | YES  |     | NULL    |                |

+----------+--------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

# 向唯一索引所在的列进行插入数据时，如果数据相同，则会报错

mysql> insert into userinfo2(name,password) values('wangys','123');
Query OK, 1 row affected (0.00 sec)

mysql> insert into userinfo2(name,password) values('wangys','123');
ERROR 1062 (23000): Duplicate entry 'wangys' for key 'name'

3.2 联合唯一索引

多列的组合唯一

mysql> create table service(

    -> id int auto_increment primary key ,

    -> name varchar(20) not null,

    -> host varchar(32) not null,

    -> port int not null,

    -> unique(host,port))

    -> engine = innodb default charset = utf8;

Query OK, 0 rows affected (0.01 sec)

mysql> desc service;

+-------+-------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+-------------+------+-----+---------+----------------+

| id    | int(11)     | NO   | PRI | NULL    | auto_increment |

| name  | varchar(20) | NO   |     | NULL    |                |

| host  | varchar(32) | NO   | MUL | NULL    |                |

| port  | int(11)     | NO   |     | NULL    |                |

+-------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

mysql> insert into service(name,host,port) values('nginx','1.1.1.1',80);

Query OK, 1 row affected (0.00 sec)

mysql> insert into service(name,host,port) values('nginx','1.1.1.1',80);

ERROR 1062 (23000): Duplicate entry '1.1.1.1-80' for key 'host'

mysql> insert into service(name,host,port) values('nginx','1.1.1.1',90);

Query OK, 1 row affected (0.00 sec)

四、primary key

唯一值
非空
可以为一列，也可以为多列（联合主键）
可以再创建表时创建，也可以基于已创建的表进行增加

4.1 创建主键的方法

第一种

mysql> create table userinfo(

    -> id int auto_increment primary key,

    -> name varchar(20))

    -> engine = innodb default charset = utf8;

Query OK, 0 rows affected (0.09 sec)

mysql> desc userinfo

    -> ;

+-------+-------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+-------------+------+-----+---------+----------------+

| id    | int(11)     | NO   | PRI | NULL    | auto_increment |

| name  | varchar(20) | YES  |     | NULL    |                |

+-------+-------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

第二种

mysql> create table userinfo2(

    -> id int auto_increment,

    -> name varchar(20),

    -> primary key(id))

    -> engine = innodb default charset = utf8;

Query OK, 0 rows affected (0.01 sec)

mysql> desc userinfo2;

+-------+-------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+-------------+------+-----+---------+----------------+

| id    | int(11)     | NO   | PRI | NULL    | auto_increment |

| name  | varchar(20) | YES  |     | NULL    |                |

+-------+-------------+------+-----+---------+----------------+

2 rows in set (0.01 sec)

第三种修改

mysql> create table userinfo3( id int, name varchar(20)) engine = innodb default charset = utf8;

Query OK, 0 rows affected (0.01 sec)

mysql> alter table userinfo3 modify id int auto_increment primary key ;

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc userinfo3;

+-------+-------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+-------------+------+-----+---------+----------------+

| id    | int(11)     | NO   | PRI | NULL    | auto_increment |

| name  | varchar(20) | YES  |     | NULL    |                |

+-------+-------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

第四种联合主键

mysql> create table services(

    -> name varchar(20),

    -> host varchar(32),

    -> port int,

    -> primary key(host,port))

    -> engine = innodb default charset=utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> desc services;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| name  | varchar(20) | YES  |     | NULL    |       |

| host  | varchar(32) | NO   | PRI |         |       |

| port  | int(11)     | NO   | PRI | 0       |       |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> insert into services(name,host,port) values('nginx','1.1.1.1',80);

Query OK, 1 row affected (0.00 sec)

mysql> insert into services(name,host,port) values('nginx','1.1.1.1',90);

Query OK, 1 row affected (0.00 sec)

mysql> insert into services(name,host,port) values('nginx','1.1.1.1',90);

ERROR 1062 (23000): Duplicate entry '1.1.1.1-90' for key 'PRIMARY'

4.2 auto_increment

约束字段为自动增长，被约束的字段必须同时被key约束

mysql> create table userinfo4(

    -> id int primary key auto_increment,

    -> name varchar(20))

    -> engine = innodb default charset = utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> desc userinfo4;

+-------+-------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+-------------+------+-----+---------+----------------+

| id    | int(11)     | NO   | PRI | NULL    | auto_increment |

| name  | varchar(20) | YES  |     | NULL    |                |

+-------+-------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

mysql> insert into userinfo4(name) values('wangys');

Query OK, 1 row affected (0.00 sec)

mysql> select * from userinfo4;

+----+--------+

| id | name   |

+----+--------+

|  1 | wangys |

+----+--------+

1 row in set (0.00 sec)

# 也可以直接指定id值

mysql> insert into userinfo4(id,name) values(4,'wangys');

Query OK, 1 row affected (0.00 sec)

mysql> select * from userinfo4;

+----+--------+

| id | name   |

+----+--------+

|  1 | wangys |

|  4 | wangys |

+----+--------+

五、外键

被关联的字段必须有唯一约束，且为innodb表

# 创建一个部门表
mysql> create table departments(

    -> id int auto_increment primary key,

    -> name varchar(25) not null)

    -> engine = innodb default charset = utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> desc departments;

+-------+-------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+-------------+------+-----+---------+----------------+

| id    | int(11)     | NO   | PRI | NULL    | auto_increment |

| name  | varchar(25) | NO   |     | NULL    |                |

+-------+-------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

# 创建一个员工表dpt_id是外键，主表是部门表，关联id列

mysql> create table employee(

    -> id int auto_increment primary key,

    -> name varchar(25) not null,

    -> dpt_id int,

    -> foreign key(dpt_id)

    -> references departments(id)

    -> on delete cascade

    -> on update cascade)

    -> engine = innodb default charset = utf8;

Query OK, 0 rows affected (0.00 sec)

mysql> desc employee;

+--------+-------------+------+-----+---------+----------------+

| Field  | Type        | Null | Key | Default | Extra          |

+--------+-------------+------+-----+---------+----------------+

| id     | int(11)     | NO   | PRI | NULL    | auto_increment |

| name   | varchar(25) | NO   |     | NULL    |                |

| dpt_id | int(11)     | YES  | MUL | NULL    |                |

+--------+-------------+------+-----+---------+----------------+

# 删除主表某一行，则自动删除员工表关联的数据

mysql> select * from departments;

+----+--------+

| id | name   |

+----+--------+

|  1 | 产品   |

|  2 | 技术   |

|  3 | 销售   |

+----+--------+

3 rows in set (0.00 sec)

mysql> select * from employee;

+----+--------+--------+

| id | name   | dpt_id |

+----+--------+--------+

|  1 | 老王   |      2 |

|  3 | 老搜   |      3 |

|  4 | 老郭   |      1 |

+----+--------+--------+

3 rows in set (0.00 sec)

mysql> delete from departments where name ='产品';

Query OK, 1 row affected (0.00 sec)

mysql> select * from departments;

+----+--------+

| id | name   |

+----+--------+

|  2 | 技术   |

|  3 | 销售   |

+----+--------+

2 rows in set (0.00 sec)

mysql> select * from employee;

+----+--------+--------+

| id | name   | dpt_id |

+----+--------+--------+

|  1 | 老王   |      2 |

|  3 | 老搜   |      3 |

+----+--------+--------+

2 rows in set (0.00 sec)

秒客网

Mysql-表的完整性约束