sql数据库完整性约束

使用SQL对数据进行完整性控制（3类完整性、CHECK短语、CONSTRAIN子句、触发器）。

用实验证实，当操作违反了完整性约束条件时，系统是如何处理的。

根据以下要求认真填写实验报告，记录所有的实验用例。

3类完整性
1.实体完整性：
（列级约束条件）
当操作违反了完整性约束条件时：
检查主码的各个属性是否为空，只要有一个为空就拒绝插入；
检查主码值是否唯一，如果不唯一则拒绝插入；
2.参照完整性:
当操作违反了完整性约束条件时：
①sc表中增加一个元组，该元组的sno属性值在表student中找不到一个元组，其sno属性值与之相等；（系统拒绝）
②sc表中修改一个元组，修改后该元组的sno属性值在表student中找不到一个元组，其sno 属性值与之相等；（系统拒绝）
③从student表中删除一个元组，造成sc表中某些元组的sno属性值在表student中找不到一个元组，其sno属性值与之相等；（系统拒绝/级联删除/设置为空值）
④从student表中修改一个元组，造成sc表中某些元组的sno属性值在表student中找不到一个元组，其sno属性值与之相等；（系统拒绝/级联删除/设置为空值）
3.用户定义的完整性：
列值非空、列值唯一、检查列值是否满足一个条件表达式（check短语）
当往表中插入元组或修改属性的值时，关系数据库管理系统将检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行：
Check语句并没有起约束作用：（在MySQL中check子句会被分析，但是是忽略的）
CONSTRAIN子句
触发器。

第五章-完整性约束⽬录1. 概述数据库的完整性指数据的正确性(correctness)和相容性(compat-ability)。

正确性：指数据符合现实语义。

相容性：指同⼀对象在不同关系表中的数据是符合逻辑的。

为了维护数据库的完整性，数据库管理系统(DBMS)必须实现如下功能：定义完整性约束在SQL标准中定义了⼀系列定义完整性约束的语句。

完整性检查检查数据是否符合完整性约束条件的机制成为完整性检查。

完整性检查通常在INSERT、UPDATE、DELETE语句执⾏后开始检查，也可在事务提交时检查。

违约处理在DBMS发现⽤户的操作违背了完整性约束条件，将采取⼀定的操作。

关系数据库管理系统使得完整性控制成为其核⼼⽀持的功能，从⽽能为所有⽤户和应⽤提供⼀致的数据库完整性。

数据库完整性主要分为：实体完整性、参照完整性和⽤户定义完整性。

在下⽂中，我将逐⼀介绍上述三类完整性，并且还会介绍⼀些SQL中定义的⼀些特性。

2. 实体完整性定义实体完整性⽰例：CREATE TABLE Student(Sno CHAR(9) PRIMARY KEY, -- 在列⼀级定义主码Sname CHAR(20),Ssex CHAR(2),Sage SMALLINT,Sdept CHAR(20)PRIMARY KEY(Sno) -- 在表⼀级定义主码);实体完整性检查和违约处理使⽤PRIMARY KEY短语定义关系的主码后。

每当⽤户插⼊或更新记录时，DBMS都会⾃动进⾏实体完整性检查：检查主码是否唯⼀，若不唯⼀便拒绝插⼊或修改。

检查主码的各个属性是否为空，若存在为空便拒绝插⼊或修改。

3. 参照完整性关系模型的参照完整性在创建表时使⽤FOREIGN KEY短语定义参照关系。

定义参照完整性⽰例：CREATE TABLE SC(Sno CHAR(9) NOT NULL,Cno CHAR(4) NOT NULL,Grade SMALLINT,PRIMARY KEY (Sno, Cno),FOREIGN KEY (Sno) REFERENCES Student(Sno), -- 定义表级参照完整性FOREIGN KEY (Cno) REFERENCES Student(Cno) -- 定义表级参照完整性);参照完整性检查如你所知，参照完整性将两个表中相应的元组联系起来。

数据库中的数据完整性与一致性约束数据完整性和一致性是数据库设计与管理中非常重要的概念。

通过在数据库中应用各种约束条件，可以保障数据的准确性、完整性和一致性。

本文将介绍数据库中数据完整性和一致性约束的概念、作用、实现方式以及相关的最佳实践。

一、数据完整性约束的概念与作用数据完整性是指数据库中数据的准确性和一致性。

数据库中的数据应该符合预先定义的规则和条件，以确保数据的有效性和可靠性。

数据完整性约束是用于限制数据库中数据的输入、更新和删除操作，以保持数据的完整性。

数据完整性约束的作用是：1. 防止非法数据的插入：通过限制输入数据的规则和条件，可以避免不符合要求的数据被插入到数据库中。

2. 保证数据的一致性：通过定义数据之间的关系和依赖，可以保证数据在更新、删除操作时的一致性，避免数据的冲突和矛盾。

3. 提高数据质量和可靠性：数据完整性约束可以帮助用户更好地管理和使用数据库，提高数据的质量和可靠性。

二、数据完整性约束的实现方式数据库中的数据完整性约束可以通过以下几种方式实现：1. 主键约束：主键是一个可以唯一标识数据库表中每一行数据的字段或字段组合。

主键约束要求主键值唯一且非空，通过主键约束可以保证数据的唯一性和完整性。

2. 外键约束：外键是一个表中的字段，它与另一个表中的主键字段相关联。

外键约束要求外键值必须在关联表中存在，通过外键约束可以保证数据的一致性和完整性。

3. 唯一约束：唯一约束要求某个字段的值在整个表中必须唯一，通过唯一约束可以防止重复数据的插入和更新。

4. 默认值约束：默认值约束可以在插入新数据时，对某个字段设置默认值，确保数据的完整性。

5. 检查约束：检查约束是对某个字段的值进行规则验证，只有符合条件的数据才能被插入和更新。

三、数据一致性约束的概念与作用数据一致性是指数据库中的数据在任何时刻都满足特定的条件和规则，数据之间的关系和依赖保持一致。

数据一致性约束是用于维护数据一致性的规则和条件。

关系数据库的完整性约束关系数据库是一种常用的数据库模型，它使用表格来存储和组织数据。

在关系数据库中，完整性约束是一种重要的机制，用于确保数据的一致性和准确性。

本文将介绍关系数据库的完整性约束的定义、类型以及它们在数据库设计和数据管理中的作用。

一、完整性约束的定义完成性约束是指定义在关系数据库表上的规则，用于保证数据的完整性。

它可以限制某些列的取值范围、规定表之间的关系、强制规则和业务规则等。

通过定义完整性约束，可以防止不一致、不准确或不符合业务规则的数据进入数据库。

二、完整性约束的类型1. 实体完整性约束实体完整性约束用于确保每一行数据在主键列上具有唯一的值，并且不为空。

主键是关系数据库中用于唯一标识每一行数据的列或列组合。

2. 引用完整性约束引用完整性约束用于确保表之间的关系的一致性。

它结合了外键和主键的概念，确保在外键列中的值必须在参照表的主键列中存在。

这样可以防止不一致的关系建立或错误的数据引用。

3. 域完整性约束域完整性约束用于限制某些列的取值范围。

可以通过定义数据类型、长度、格式等约束来确保数据的准确性和一致性。

4. 用户自定义完整性约束用户自定义完整性约束是根据特定业务需求定义的约束规则。

可以使用触发器、存储过程或触发器等数据库对象来实现自定义完整性约束。

5. 断言完整性约束断言完整性约束是在SQL标准中定义的，用于在关系数据库中表示某种条件必须成立。

断言可以在表创建时定义或随后添加，以确保数据满足特定条件。

三、完整性约束的作用1. 数据的一致性和准确性：通过完整性约束，可以限制数据的取值范围和关系，确保数据的准确性和一致性。

2. 数据的安全性：完整性约束可以防止数据被非法篡改、删除或插入不符合规定的数据。

3. 业务规则的保护：通过完整性约束，可以确保数据库中存储的数据符合特定的业务规则。

4. 避免错误的数据关联：引用完整性约束可确保外键的合法性，防止不一致的数据关联。

5. 数据的可靠性和可维护性：通过定义完整性约束，可以为数据库提供可靠的结构和规范，便于数据的管理和维护。

sql完整性约束课本中把完整性约束单独做成⼀章来讲解，所以之前写数据定义之基本表定义时说会放到后⾯讲。

完整性约束有三种：实体完整性，参照完整性，⽤户定义完整性。

最后会讲完整性约束命名⼦句，所以⼀共四个部分。

Part [实体完整性]实体完整性即主码，⽤primary key定义。

可⽤两种⽅式：列级约束：create table test (id int primary key); 只能选取⼀列作为主码；表级约束：create table test (id int,name char(10),primary key(id,name)); 可选多列作为主码；Part [参照完整性]参照完整性即外码，⽤foreign key(<列名>[,<列名>...]) references <表名>(<列名>[,<列名>..]);⽽且只能使⽤表级约束。

现在以上⾯那个截图中的表为参照，建⽴另⼀个表　注意：外码⼀定参照主码（可以是其他表的主码，也可以时⾃⼰的）,⽽且外码的列数⼀定要等于被参照表的主码列数。

　参照完整性中⽐较⿇烦的是违约处理，即当对被参照表进⾏update/delete/insert操作会破坏参照完整性时，参照表赢告诉被参照表应该怎么做。

　所以参照表可以在定义外码时添加上on delete/update [<no action> / <cascade>]。

　看例⼦⽐较直观：　其中no action 表⽰拒绝执⾏（为默认值），cascade表⽰级联操作。

Part [⽤户定义完整性]书中的⽤户定义完整性有三种：列值⾮空（not null),列值唯⼀（unique),列值需满⾜条件表达式（check);not null都是列级完整性约束，很好理解。

check和unique可以是列级，也可以是表级约束。

看例⼦很⽅便理解：　除了这三种，现在的sql产品中很多都⽀持⼀个设置默认值的操作（default);　如果不设置，默认为NULL（not null,primary key除外）。

SQL⼊门（3）：定义约束断⾔assertion触发器trigger 本⽂介绍数据库的完整性完整性控制程序: 指定规则,检查规则(规则就是约束条件)动态约束 intergrity constraint::=(O,P,A,R)O : 数据集合, 约束的对象 ?: 列, 多列的元组集合P: 谓词条件: 什么样的约束?A: 触发条件: 什么时候检查?R: 响应动作: 不满⾜怎么办?按照约束对象分类:(1)域完整性约束条件: 施加在某⼀列上, ⽐如sage<25 and sage<40(2)关系完整性约束条件: 施加在表上, 涉及多列, 2<=hours/credit<=16按照约束来源分类:(1)结构约束: 主键约束, 外键约束,是否允许空值等 primary key, foreign key, not null(2) 内容约束: 取值范围, check(sage<25 and sage<40)按照约束状态分类:(1) 静态约束: 要求DB在任何时候都要满⾜的约束(2) 动态动态: DB改变状态时要满⾜的约束, 例如salary 只能加不能减, 不能由1000改为500.---> 触发器SQL⽀持如下⼏种约束:静态约束中的列完整性与表完整性, 动态约束中的触发器(⼀) 静态约束实现: create table(1) col_constr 列约束 (⼀种域约束类型, 对单⼀列的值进⾏约束)not null 列值⾮空primary key 主键not null + unique 就是⾮空+唯⼀性 ,实际上就是⼀个主键check (search_condition) 列值满⾜的条件,references tablename(colname) , colname 是tablename 的主键on delete[ cascade| set null], 则删除被引⽤表的某⼀列v值时, 要将本表该列值为v 的记录删除或者列值更新为null, 缺省为⽆操作.例: 创建⼀个表 studentcreate table student (sno char(8) not null unique, sname char(10),--not null unique 表⽰主键ssex char(2) constraint ctssex check(ssex='男'or ssex='⼥'),-- ctssex 是约束constraint 的名字. 之后可以单独处理sage integer check(sage>=1and sage<150), -- 没有定义约束名, 则之后不能单独处理dno char(2) references dept(dno) on delete cascade, -- dno 在dept表中是主键,sclass char(6));on delete cascade表⽰如果dept表中的某个'01'系被删除了,那么在student 表中该系所有学⽣的dept 值为null, 如果没有加这个, 那么dept表中的删除操作对student表没有影响.create table course (cno char(3), cname char(10), chours integer,credit float(1) constraint ctcredit check(credit>=1.0and credit <=6.0),tno char(3) references teacher(tno) on delete cascade);-- 或者通过alteralter table course add constraintcs_tno foreign key(tno) references teacher(tno) on delete cascade;-- 移除约束alter table course drop constraint cs_tno;补充: unique 和not nullcreate table tbl1(name1 varchar(10), num1 varchar(10),constraint cs_num1 unique(num1));-- 或者create table tbl1(name1 varchar(10), num1 varchar(10) unique);-- 之后再添alter table tbl1 add constraint cs_num1 unique(num1);alter table tbl1 drop constraint ;-- ⾮空约束create table tbl1(name1 varchar(10), num1 varchar(10) not null);-- 新增⾮空约束alter table tbl1 modify num1 not null;-- 删除⾮空约束不是⽤dropalter table tbl1 modify num1 null;(2) table_constr 表约束, ⽤于多列或者元组的值进⾏约束create table student ( sno char(5) not null unique,sname char(5),ssex char(2) constraint ctssex check(ssex='男'or ssex='⼥') ,sage integer check(sage>1and sage<150).dno char(3) references dept (dno) on delete cascade,sclass char(5),primary key(sno));--primary key(sno) 可以放在sno 这⼀列的后⾯, 也可以放在最后这⾥, 看成是表约束create table course (cno char(3), cname char(10), chours integer,credit float(1) constraint ctcredit check(credit>=1.0and credit <=6.0),tno char(3) references teacher(tno) on delete cascade,primary key (cno), constraint ctcc check(chours/credit=12));-- 严格约束12课时对应1个学分create table sc(sno char(5), cno char(3),score float(1) constraint ctscore check(score>=0.0and score<=100.0),foreign key(sno) references student(sno) on delete cascade,foreign key(cno) references course(cno) on delete cascade);注意: check 后⾯的条件可以是select from where 语句create table sc(sno char(5) check (sno in (select sno from student)),cno char(3), check(cno in (select cno from course)), --相当于外键score float(1) constraint ctscore check(score>=0.0and score<=100.0);注意: create table 中的约束条件可以在后⾯根据需要进⾏撤销 ,也可以追加约束alter table tablename +add 追加约束, 也可以增加新的⼀列drop 删除⼀列的约束,或者删除⼀列,modify 修改alter table sc drop constraint ctscore; -- 撤销对score的约束ctscore;alter table scmodify ( score float(1) constraint nctscore check(score>0.0and score<=150.0));-- 修改约束alter table scadd ( score float(1) constraint nctscore check(score>0.0and score<=150.0));-- 增加约束(3) 断⾔ assertion⼀个断⾔就是⼀个谓词表达式, 它表达了希望数据库总能满⾜的条件, 表约束与列约束就是⼀些特殊的断⾔.还有复杂的断⾔ create assertion [assertion name] check [predicate]那么之后数据库的每⼀次更新去判断是否违反该断⾔, 断⾔测试增加了数据库维护的负担, 没事不要使⽤!! 例如: 每位教师同⼀时间段不能在两个不同的地⽅上课.实例1: 已知下列4张表borrower(client_name,loan_num)客户以及他的贷款account(account_num,balance) 账户和余额depositor(account_num, client_name) 账户与客户名loan(loan_num, amount) 每⼀笔贷款现在规定: 每⼀笔贷款 , 要求⾄少这个借款者的账户中有最低余额500元.create assertion balance_cst check(not exists (select*from loanwhere not exists (select*from borrower b, depositor d, account awhere loan.loan_num=b.loan_num andb.client_name=d.client_name anda.account_num=d.account_num and a.balance>=500)));实例2: 现有3张表account(branch_name, account_num, balance) 分⾏账户与余额loan(branch_name,loan_num,amount) 分⾏的每⼀笔贷款branch(branch_name,..) 分⾏信息每⼀个分⾏的贷款总量要⼩于该分⾏所有账户的余额总额 (不存在某⼀个分⾏它的贷款额⼤于余额) create assertion sum_cst check(not exists (select*from branch where (select sum(amount) from loanwhere loan.branch_name=branch.branch_name) >=(select sum(balance) from account where account.branch_name=branch.branch_name)));(⼆) 动态约束以上 create table 中的表约束与列约束是静态约束, 下⾯介绍动态约束-->触发器 trigger动态约束是⼀种过程完整性的约束, 相⽐之下, 之前的create table 的约束是⾮过程性约束动态约束是⼀个程序, 该程序可以在特定的时刻被⾃动触发执⾏: ⽐如在⼀次更新之前,或者⼀次更新之后的执⾏.动态约束 intergrity constraint::=(O,P,A,R), O P A R 都需要定义, 再来回顾下O : 数据集合, 约束的对象 ?: 列, 多列的元组集合P: 谓词条件: 什么样的约束?A: 触发条件: 什么时候检查?R: 响应动作: 不满⾜怎么办?以下是Oracle 的触发器的语法例⼦, 在SQL server中语法略有差别, 但是思路⼀致. ..创建触发器的基本语法:create trigger trigger_namebefore| after [insert | delete|update] [of colname] on tablenamefor each row| for each statementwhen [search_condition][statement][begin atomic statement; ... end;] --多个条件注意: row , as 可以省略!实例(1):当teacher表更新元组时, 控制其⼯资只能涨不能跌create trigger teacher_sal-- 触发器名字before update of salary on teacher --作⽤在什么表的什么列referencing new x, old y -- 定义更新前后的值for each row when(x.salary<y.salary) -- 对每⼀条记录都要检查,begin--如果违反则执⾏raise_application_error(-20003,'invalid salary on update');-- Oracle的错误处理函数, 提⽰⽆效更新end;实例(2) : student(sno, sname, sumcourse), sumcourse 表⽰该同学已经学习的课程门数,初始值是0, 以后每修⼀门课都要对其+1, 设计⼀个触发器⾃动完成这个功能.create trigger sumcafter insert on sc -- 对于sc 的新增信息作出反应referencing new row newI-- 定义更新后的⾏=newifor each rowbegin-- 执⾏操作update student set sumsourse=sumcourse+1where sno=:newi.sno; -- 这条记录(⾏)对应的学号end;实例(3) :student(sno, sname, sage,ssex,scalss) 中某⼀个学⽣变动其主码sno,则在sc 表中该同学的学号也要相应改变create trigger upd_snoafter update of sno on student --指明更新的地⽅referencing old oldi, new newifor each rowbeginupdate sc set sno=newi.sno where sno=: oldi.sno;end;实例(4) :student(sno, sname, sumcourse)中删除某⼀个学⽣sno时, 在sc 中该学⽣的选课记录也要删除create trigger del_snoafter delete on studentreferencing old oldifor each rowbegindelete sc where sno=:oldi.sno;end;实例(5) : student(sno, sname, sumcourse)中删除某⼀个学⽣sno时, 在sc 中该学⽣的sno设置为null create trigger del_snoafter delete on studentreferencing old oldifor each rowbeginupdate sc set sno=null where sno=:oldi.sno;end;实例(6) :假设有两张表, dept(dno,dname,dean) ,该表字段是系号系名系主任名, 以及teacher(tno,tname,dno,salary) . 现在需要控制在对dept 的dean 做更新的时候,必须满⾜dean 的⼯资是同⼀系⾥最⾼的, 否则更新报错.create trigger dean_salbefore update of dean on dept -- 对dept 的dean 做更新的时候referencing old oldi , new newi -- 更新前后的新旧定义for each row when(dean not in(select tname from teacher where dno=:newi.dno and salary>=all(select salary from teacher where dno=:newi.dno )))-- 同系教师⼯资begin-- 不满⾜条件时raise_application_error(-20003,'invalid dean on update');end;。