数据库完整性基于关系型数据库mysql

数据库的完整性是指数据的正确性和相容性，是为了防止数据库中存在不符合语义的数据，即不正确的数据。

为了提供数据库的完整性，数据库管理系统必须能实现如下功能：

1. 提供定义完整性约束条件的机制
2. 提供完整性检查的方法
3. 进行违约处理

• 实体完整性【主键primary key】

将检查主码值唯一且不为空，检查方法为避免全表扫描【耗时】将自动建立一个索引，违约处理为拒绝该操作。

对单属性构成的码可以有两种说明方法，列级约束条件和表级约束条件；对多个属性构成的码只有定义为表级约束条件的说明方法。写法如下：

create table Student1(
  Sno char(32) primary key,/*单属性列级定义主码*/
  Sname varchar(32),
  ...
);

create table Student2(
  Sno char(32),
  Sname varchar(32),
  ...,
  primary key(Sno,Sname)/*多属性表级定义主码*/
);

...
primary key(Sno)/*单属性表级定义主码*/
...

• 参照完整性【外键 foreign key】

参照完整性将两个表中相应元组联系起来。

写法如下：

create table SC(
  Sno char(32),
  Sname varchar(32),
  ...
  primary key(Sno,Sname),/*表级定义实体完整性*/
  foreign key(Sno) references Student2(Sno),/*表级定义参照完整性*/
  foreign key(Sname) references Student2(Sname)/*表级定义参照完整性*/
);

对两个表进行操作都有可能破坏参照完整性，必须进行检查以保证两表的相容性，如上例所示，四种破坏参照完整性的情况：

1. SC表中增加一个元组，该元组的Sno属性值在表Student中找不到一个元组，其Sno属性值与之相等。
2. 修改SC表中一个元组后，修改后的Sno属性值在表Student中找不到一个元组，其Sno属性值与之相等。
3. 从Student表中删除一个元组，造成SC表中某些元组的Sno属性值在表中Student中找不到一个元组，其Sno属性值与之相等。
4. 修改Student表中一个元组的Sno属性，造成SC表中某些元组的Sno属性值在表中Student中找不到一个元组，其Sno属性值与之相等。

当上述不一致发生时，系统可以采取以下策略加以处理：

1. 默认策略，拒绝执行
2. 级联操作，删除或修改参照表【如:上例的SC】中的所有不一致的元组
3. 设置为空值，将参照表中所有造成不一致的元组的对应属性值设置为空值

显示说明参照完整性的违约处理写法如下：

create table SC(
  Sno char(32),
  Sname varchar(32),
  Cno char(32)
  ...
  primary key(Sno,Cno),/*表级定义实体完整性*/
  foreign key(Sno) references Student2(Sno) /*表级定义参照完整性*/
    on delete cascade /*删除Student2表中的元组时，级联删除SC表中相应的元组*/
    on update cascade,/*更新Student2表中的Sno，级联更新SC表中相应的元组*/
  foreign key(Cno) references Course(Cno)/*表级定义参照完整性*/
    on delete no action /*删除Course表中的元组造成与SC属性值不一致时，拒绝删除*/
    on update cascade /*更新Course表中的Cno时，级联更新SC表中相应的元组*/
);

• 用户定义的完整性

针对某一具体应用的数据必须满足的语义要求。

主要包含列值非空（not null）、列值唯一（unique）、检查列值是否满足一个条件表达式（check短语），违约处理是将拒绝执行操作，写法如：

create table Student1(
  Sno char(32),
  Sname varchar(32) not null unique,/*名字既非空也要唯一*/
  Ssex varchar(32) check(Ssex in ('男','女'))/*属性上性别只能取男或者女*/
);

create table Student2(
  Sno char(32),
  Sname varchar(32) not null unique,/*名字既非空也要唯一*/
  Ssex varchar(32) check(Ssex='女' or Sname not like 'Ms.%')/*性别为女 或者 性别为男但是名字不以Ms.开头 都可以通过该项check检查*/
);

 

• 完整性约束命名子句

 对完整性约束子句命名，从而可以灵活添加删除。写法如下：

create table Student( /*创建*/
  Sno numeric(6)
    constraint c1 check(Sno between 90000 and 99999), /*Sno属性值在90000到99999之间*/
  Sname char(20)
    constraint c2 not null, /*Sname属性值非空*/
  Sage numeric(3)
    constraint c3 check(Sage<30), /*Sage属性值小于30*/
    constraint c4 primary key(Sno), /*设置Sno为主键*/
    constraint c5 foreign key(Sno) references SC(Sno) /*设置Sno为外键*/
);

alter table Student/*去掉约束条件c5（外键）*/
  drop constraint c5;

alter table Student/*增加约束条件c5*/
  add constraint c5 foreign key(Sno) references SC(Sno);

• 域中的完整性限制

域是一组1具有相同数据类型的值的集合，使用create domain语句创建一个域及该域应满足的完整性约束条件，可以使用域来定义属性。优点是，数据库中不同的属性可以来自同一域，当域的完整性条件改变时只要修改域的定义即可，而不必一一修改域上的各个属性。写法如下：

/*例1*/
create domain GenderDomain varchar(32) /*创建一个名为GenderDomain的域*/
  check(value in ('男','女'));

/*创建以后即可使用*/
create...(
  ...
  Ssex GenderDomin,
  ...
);

/*例2*/
create domain GenderDomain/*创建一个名为GenderDomain的域，并为其约束条件命名为GD*/
  constraint GD check (value in ('男','女'));

/*删除域的约束条件GD*/
alter  domain GenderDomain
  drop constraint GD;
/*增加约束条件GDD*/
alter  domain GenderDomain
  add constraint GDD  check(value in ('1','0'));

• 断言

使用create assertion语句，通过声明性断言来指定更具一般性的约束，可以定义涉及多个表或聚集操作的比较复杂的完整性约束，任何断言不为真的操作都会被拒绝执行。写法如下：

create assertion ass_SC_CNUM /*创建断言，每学期每门课最多60个学生选修*/
  check (60>=all(select count(*) from SC group by cno,term));

drop assertion ass_SC_CNUM;/*删除断言*/

• 触发器

用户定义在关系表上的一类由时间驱动的特殊过程，，一旦定义就被保存在数据库服务器中。

我以前写过一个简单的完整触发器操作【当插入一条记录到表tig_test时，表tig_act将自动添加一条数据】，懒得再写就直接复制到下面了：

1、在cmd打开mysql数据库
2、创建一个数据库more_test
drop database more_test if exists more_test;
create database more_test;
3、切换到新的数据库
use more_test;
4、创建一个表
create table tig_test(id int,name varchar(32));
5、插入数据
insert into tig_test values(1,'你');
insert into tig_test values(2,'好');
insert into tig_test values(3,'是');
6、触发器语法体
create trigger 触发器名称
after/before 触发事件【insert/update/delete/insert or update等】 on 表名
referencing new/old row as 变量
for each row/statement
begin
   ...
end;
7、创建一个触发器，注意---先改变操作结束的字符为$，使用语句 DELIMITER $ 即可

create trigger trig_Fir 
after insert on tig_test
for each row
begin
insert into tig_act values(999,'触发事件了大哥');
end$

8、将mysql操作结束的字符串改回原来的分号 DELIMITER;
9、测试：插入一条记录到表tig_test
insert into tig_test values(4,'测试');
10、检查：事件已经触发，查看表tig_act已经自动添加了一条数据
select * from tig_act;
11、查看触发器
show triggers;
12、删除该触发器
drop triggers trig_Fir on tig_test;