数据库-表的约束

1.概念

1.为什么需要约束

        真正约束字段的是数据类型,但是数据类型约束很单一,需要有一些额外的约束,更好的保证数据的合法性,从业务逻辑角度保证数据的正确性，如：有一个字段是身份证,要求是唯一的。

定义字段的类型无法满足对表的约束：
       · 表字段是否可以为NULL,有没有默认值,表字段的解释能不能加上。
       · 对于数字类型的字段可不可以指定默认表示的位数,可不可以将这个字段设置称为唯一标识该行的数据。

        本质上Mysql是一套数据存储解决方案,除了解决基本的数据存储功能之外,还要保证数据尽可能的安全,减少用户的误操作可能性, 约束的体现,不仅仅可以体现在数据库层面,在我们进行用户业务逻辑编码的时候,我们其实也可以做判断(约束),约束其实不仅仅只能出现在数据库层而上,编码层面也是可以体现。

2.数据的完整性

·实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
·域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
·引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
·用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理
的工资不得高于本部门职工的平均工资的5倍。

2.常见约束

1.非空约束

若需要某些字段必须写入，应该怎么处理，如：创建一个班级表，包含班级名和班级所在的教室，站在正常的业务逻辑中：如果班级没有名字，你不知道你在哪个班级，如果教室名字可以为空，就不知道在哪上课，所以我们在设计数据库表的时候，一定要在表中进行限制，满足上面条件的数据就不能插入到表中，这就是“约束”。

mysql> create table myclass1(class_name varchar(20) not null, class_room
varchar(20) not null, class_id int); # 无约束
Query OK, 0 rows affected (0.02 sec)
mysql> desc myclass1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO | | NULL | |
| class_room | varchar(20) | NO | | NULL | |
| class_id | int | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 插入数据时：
mysql> insert into myclass1 (class_room) values('计科1');
ERROR 1364 (HY000): Field 'class_name' doesn't have a default value 
# 失败，因为name有约束不能为空
mysql> insert into myclass1 (class_name,class_room) values('A104','计科1'); 
#同时插入成功
Query OK, 1 row affected (0.00 sec)
mysql> select * from myclass1;
+------------+------------+----------+
| class_name | class_room | class_id |
+------------+------------+----------+
| A104 | 计科1 | NULL |
+------------+------------+----------+
1 row in set (0.00 sec)
# id字段无约束，可以为空

2.默认值约束

作用：给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

mysql> create table teacher1(id int not null, name varchar(30) not null, sex
char(2) default '女');
Query OK, 0 rows affected (0.03 sec)
mysql> desc teacher1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(30) | NO   |     | NULL    |       |
| sex   | char(2)     | YES  |     |  女     |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into teacher1(id,name) values('1001','林雪');
Query OK, 1 row affected (0.00 sec)
mysql> select * from teacher1;
+------+--------+------+
| id   | name   | sex |
+------+--------+------+
| 1001 | 林雪    | 女 |
+------+--------+------+
1 row in set (0.00 sec)
mysql> insert into teacher1(id,name,sex) values('1002','石磊','男');
# 也可以全字段插入
Query OK, 1 row affected (0.01 sec)
mysql> select * from teacher1;
+------+--------+------+
| id   | name   | sex |
+------+--------+------+
| 1001 | 林雪    | 女 |
| 1002 | 石磊    | 男 |
+------+--------+------+
2 rows in set (0.00 sec)

3.主键约束

作用：数据库存储数据不是唯一的目的,还要考虑到提取数据，一般要求数据库表中的记录要有一个特定的唯一标识，来表示唯一性,这个唯一的特定标识就是主键,类似于序号学号这样的唯一标识,可以根据主键来唯一地筛选出一条记录.

mysql> create table t1 ( id int unsigned primary key comment '学号不能为空', name
varchar(20) not null ,sex char(2) default '男');
Query OK, 0 rows affected (0.02 sec)
mysql> desc t1;
+-------+--------------+------+-----+---------+-------+
| Field |     Type     | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   | PRI | NULL    |      |
| name  | varchar(20)  | NO   |     | NULL    |      |
| sex   | char(2)      | YES  |     | 男      |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into t1 values(1,'孙文','女');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | 孙文 | 女 |
+----+--------+------+
1 row in set (0.00 sec)


mysql> insert into t1(id,name) values(1,'黎明');
ERROR 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY'
# 再次插入出错，因为需要保持唯一性

通过修改表，来追加主键。 

mysql> alter table teacher1 add primary key(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc teacher1;
+-------+-------------+------+-----+---------+-------+
| Field |    Type     | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(30) | NO   |     | NULL    |       |
| sex   | char(2)     | YES  |     | 女      |       |
| age   | int         | NO   |     | 18      |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4.主键自增长 

 先增加主键，再给主键增加自增长

# 先增加主键
mysql> use mydb2_stuinfo;
Database changed
mysql> alter table student1 modify id int primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 设置自增长
mysql> alter table student1 modify id int auto_increment;
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
# 查看
mysql> desc student1;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default |      Extra     |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30)  | YES  |     | NULL    |                |
| sex      | char(2)      | YES  |     | 女      |                |
| age      | int unsigned | YES  |     | NULL    |                |
| score    | float        | YES  |     | NULL    |                |
| addr     | varchar(50)  | YES  |     | NULL    |                |
| passwd   | char(8)      | YES  |     | NULL    |                |
| birthday | date         | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> insert into student1(name) values('关六');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student1;
# 发现自动增长

5.外键约束

作用：限定某个表的某个字段的引用完整性

foreign key (自身表的字段名称) references 主表名字(参考表的字段名称) # 建立外键关联

# 定义主表
mysql> create table myclass3(id int primary key, name varchar(20) not null comment
'班级名');
Query OK, 0 rows affected (0.02 sec)
mysql> desc myclass3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

定义从表：
mysql> create table myclass3_stu( id int primary key, name varchar(30) not null
comment '学生名', class_id int, foreign key (class_id) references myclass3(id) );
Query OK, 0 rows affected (0.02 sec)
mysql> desc myclass3_stu;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(30) | NO   |     | NULL    |       |
| class_id | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

6.唯一约束

mysql> create table t4( id int primary key, name varchar(20) unique comment '名子不
能重名，可以为空' );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 插入数据
mysql> insert into t4 values(1,'刘文');
Query OK, 1 row affected (0.00 sec)
# 唯一键数据重复，插入失败
mysql> insert into t4 values(1,'刘文');
ERROR 1062 (23000): Duplicate entry '1' for key 't4.PRIMARY'

7.检查约束

创建表时设置检查约束

mysql> create table t5(id int primary key, name varchar(20), salary float,
check(salary>0 and salary<10000) );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t5;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t5 values(1,'Li',5984);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5 values(2,'wang',12000);
ERROR 3819 (HY000): Check constraint 't5_chk_1' is violated.