数据库三 数据表完整性

数据完整性

 

实体完整性：标识符或者主键的完整性，使其值唯一

域完整性：限制类型、格式和取值范围

引用完整性：保持原表和引用表数据一致

用户自定义完整性：根据用户要求自定义的规则

 

数据完整性实现

实体完整性：主键约束、唯一值约束

域完整性：检查约束、默认值约束、外键约束

引用完整性：外键约束

用户自定义完整性：以上所有约束组合

————————————————————————————————————————————————————————

非空约束：not null

create table teacher(t_no int not null, t_name varchar(30));

唯一约束

指定某一列或者某几列的数据不能重复，关键字： unique

1、

创建表时，指定： create table tacher(i_no int not null unique, t_name varchar(30));

desc查看： +--------+------------+------+------+--------+-------+ | Field   | Type       | Null  | Key | Default | Extra | +--------+------------+------+------+--------+-------+ | i_no   | int(11)     | NO  | PRI  | NULL   |       | | t_name | varchar(30) | YES |      | NULL   |       | +--------+------------+------+------+--------+-------+

RPI是主键标志 UNI是唯一约束，都可以是多列的组合   区别:UNI值可以为空，但只能一个为空，且UNI可以有多个；PRI值不能为空，且一个表中只能有一个

 

alter table teacher modify column t_name varchar(30)  unique;

+--------+------------+------+------+--------+-------+ | Field   | Type       | Null  | Key | Default | Extra | +--------+------------+------+------+--------+-------+ | i_no   | int(11)     | NO  | PRI  | NULL   |       | | t_name | varchar(30) | YES | UNI  | NULL   |       | +--------+------------+------+------+--------+-------+

此时t_name显示UNI，插入唯一 一个空值是允许的

 

复合唯一约束

alter table teacher add unique(t_no,t_name);

+--------+------------+------+------+--------+-------+ | Field   | Type       | Null  | Key | Default | Extra | +--------+------------+------+------+--------+-------+ | i_no   | int(11)     | YES | MUL | NULL   |       | | t_name | varchar(30) | YES |      | NULL   |       | | i_age  | int(11)     | YES |      | NULL   |       | +--------+------------+------+------+--------+-------+

两列组合唯一约束，但只有i_no字段显示MUL，这样看不出来约束关系

可通过show查询 show index from teacher;

+---------+------------+-----------+-------------+--------------+---------+-----------+----------+--------+------+-----------+----------+---------------+--------+ | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null  | Index_type | Comment | Index_comment | Visible | +---------+------------+-----------+-------------+--------------+---------+-----------+----------+--------+------+-----------+----------+---------------+--------+ | teacher |          0 | i_no      |           1 | i_no         | A      |         0 | NULL     | NULL   | YES | BTREE    |          |               | YES   | | teacher |          0 | i_no      |           2 | t_name       | A      |         0 | NULL     | NULL   | YES | BTREE    |          |               | YES   | +---------+------------+-----------+-------------+--------------+---------+-----------+----------+--------+------+-----------+----------+---------------+--------+

可以看到key_name都是i_no,即可以通过i_no删除唯一约束

drop index i_no on teacher; 或者 alter table teacher drop index i_no;

constraint 关键字

alter table teacher add constraint uni_q unique(i_no,t_name);

给唯一约束起个别名，这样就方便以后删除唯一约束

+--------+------------+------+------+--------+-------+ | Field   | Type       | Null  | Key | Default | Extra | +--------+------------+------+------+--------+-------+ | i_no   | int(11)     | YES | MUL | NULL   |       | | t_name | varchar(30) | YES |      | NULL   |       | +--------+------------+------+------+--------+-------+

此时i_no显示为MUL 意为可重复，但i_no t_name组合的值不能重复

删除唯一约束

alter table teacher drop index uni_q；

+--------+------------+------+------+--------+-------+ | Field   | Type       | Null  | Key | Default | Extra | +--------+------------+------+------+--------+-------+ | i_no   | int(11)     | YES |      | NULL   |       | | t_name | varchar(30) | YES |      | NULL   |       | +--------+------------+------+------+--------+-------+

 

主键约束 primary key

 

create table teacher(i_no int primary key, s_name varchar(30)); 或者 alter table teacher add primary key(i_no)；

+---------+------------+------+------+--------+-------+ | Field    | Type       | Null  | Key | Default | Extra | +---------+------------+------+------+--------+-------+ | i_no    | int(11)     | NO  | PRI  | NULL   |       | | s_name | varchar(30) | YES |      | NULL   |       | +---------+------------+------+------+--------+-------+

设为主键后即不能为空，同not null unique

 

删除主键

alter table teacher drop primary key;

+---------+------------+------+------+--------+-------+ | Field    | Type       | Null  | Key | Default | Extra | +---------+------------+------+------+--------+-------+ | i_no    | int(11)     | NO  |      | NULL   |       | | s_name | varchar(30) | YES |      | NULL   |       | +---------+------------+------+------+--------+-------+

删除主键不需要指定主键名字； 且删除后原主键依然限定不可为空。

自增列 auto_increment 默认从1开始，可以设置起始值

create table teacher(i_no int primary key auto_increment, s_name varchar(30));

+---------+------------+------+------+--------+---------------+ | Field    | Type       | Null  | Key | Default | Extra         | +---------+------------+------+------+--------+---------------+ | i_no    | int(11)     | NO  | PRI  | NULL   | auto_increment | | s_name | varchar(30) | YES |      | NULL   |               | +---------+------------+------+------+--------+---------------+

insert into teacher values(null,"张彪");

注意:auto_increment只能在创建时添加

检查约束 check

create table teacher(i_no int, s_name varchar(30),i_age int check(i_age>0 and i_age<200), s_sex varchar(4) check(s_sex='男' or s_sex='女'));

然而 mysql不支持check ，需要在外部进行判断

 

默认值约束   defautl

create table teacher(i_no int, s_name varchar(30), s_class varchar(30) default "c++");

 

外键约束 foreign key  .. references 

一个表中的属性值参考另一个表中的主键值

create table class(i_no int primary key, s_name varchar(10)); create table student(i_no int, s_name varchar(30), i_classNo int);   alter table student add foreign key(i_classNo)references class(i_no);

涉及的问题：父表的删除和更新   此时若删除父表的某行，因为外键约束关系，会提示无法删除活更新。

有三种方式解决： 1、cascade    子表跟着父表删除或更新 2、no action  无动作，如果子表有记录，子表也不进行删除或更新 3、set null    设置为null，如子表有记录，则设置为空 这些操作是对于父表的删除或者更新而言的，使用格式：on update (cascade/no action/set null) 、 on delete (cascade/no action/set null) 如：on update cascade        on delete set null

alter table student add foreign key(i_classno) references class(i_no) on update cascade;

此时更新class表的i_no字段的值，如果student表中有记录会同步跟新。