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.