数据库完整性及相关SQL语句

最新推荐文章于 2024-05-19 14:01:25 发布

FDR_Enterprise

最新推荐文章于 2024-05-19 14:01:25 发布

阅读量3.8k

点赞数 7

文章标签： sql sqlserver

本文链接：https://blog.csdn.net/FDR_Enterprise/article/details/105337113

版权

文章目录

数据库完整性SQL语句

数据库完整性SQL语句

本篇文章记录了第十三次作业
使用的数据库是SQL Server，使用的数据库管理软件是SQL Server Management Studio.

数据库完整性(ntegrity)指的是数据的正确性(correctness)和相容性(compat-ability). 正确性指的是数据是符合实际情况有意义的，相容性指的是数据库同一对象在不同关系表中的数据是符合逻辑的。
数据库的完整性是为了防止数据库中出现不正确以及不符合实际意义的数据。数据库需要实现以下功能以维护数据库的完整性：

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

这第一种功能就是我们之前接触到的数据库的完整性约束。第二中功能就是我们之前在更新删除SQL语句那一篇中提到的在执行这些语句的过程中DBMS会自动检查是否违反完整性约束条件。第三种是对违反完整性的操作进行处理的功能。
在一开始了解数据库的基本概念的时候我们曾讲到一些关于完整性的概念。当时说的是由于参照完整性外码要么为空要么为被参照的主码。之前由于学习的不够深入所以只粗略概括了一下数据库的三类完整性约束，这回我们带着SQL语句再去学习一下数据库完整性。

实体完整性

相关的概念我们在之前已经了解过了，这回是使用SQL语句来定义完整性。

定义实体完整性

实体完整性我们知道是跟主码有关的，而在我们建表的时候是要定义主码的，这种情况实际上就是定义实体完整性。对于单属性构成的码由两种说明方法，一种是定义为列级约束，一种是定义为表级约束。而对多个属性构成的码则是只有定义为标记约束条件这一种说明方法。

[例 5.1] 将Student表中的Sno属性定义为码

	CREATE TABLE Student
		(Sno CHAR(9) PRIMARY KEY,	/*在列级定义主码*/
		 Sname CHAR(20) NOT NULL,
		 Ssex CHAR(2),
		 Sage SMALLINT,
		 Sdept CHAR(20)
		);
	/*或者*/
	CREATE TABLE Student
		(Sno CHAR(9),
		 Sname CHAR(20) NOT NULL,
		 Ssex CHAR(2),
		 Sage SMALLINT,
		 Sdept CHAR(20)
		 PRIMARY KEY(Sno)	/*在表级定义主码*/
		);

[例 5.2] 将SC表中的Sno，Cno属性组定义为码

由于这里的主码是由两个属性组成的，所以我们只能在表级定义主码。

	CREATE TABLE SC
    	(Sno CHAR(9) NOT NULL, 
         Cno CHAR(4) NOT NULL,  
         Grade SMALLINT,
         PRIMARY KEY (Sno,Cno)    /*只能在表级定义主码*/
        );

实体完整性检查和违约处理

定义了主码后用户每次若要修改表内的相关数据，RDBMS就会像我们之前说的那样对实体完整性规则进行检查。它会检查主码是否唯一，以及主码的各个属性是否为空，只要任意一个不符合实体完整性，那么RDBMS就会拒绝这次操作。
由于全表扫描较慢而完整性检查要求尽可能快的速度，RDBMS会建立各种索引来加快检查速度。

参照完整性

参照完整性就是我们之前学习FOREIGN KEY 的时候涉及到的完整性规则。在我们定义外码的时候就定义了参照完整性。

定义参照完整性

[例 5.3] 定义SC中的参照完整性

定义中我们使用REFERENCES来设定被参照表的属性

	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 Course(Cno)	/*在表级定义参照完整性*/
        );

参照完整性检查和违约处理

因为参照完整性涉及到被参照表和参照表两个表，因此对这两个表的增删改操作均有可能破会参照完整性

当这种情况发生时,RDBMS的默认操作是拒绝此次操作。如果要让系统采用别的操作的话，需要在定义的时候进行说明。

[例 5.4] 显式说明参照完整性的违约处理示例

	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) 
		 	ON DELETE CASCADE	/*删除Student内元组时，级联删除SC表中相应的元组*/
            ON UPDATE CASCADE,   /*更新Student内元组时，级联更新SC表中相应的元组*/
         FOREIGN KEY (Cno) REFERENCES Course(Cno)	                    
            ON DELETE NO ACTION		/*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
            ON UPDATE CASCADE		/*当更新course表中的cno时，级联更新SC表中相应的元组*/
        );

可以看到定义处理方式的形式是不同的，这就需要我们根据需求来确定处理方式。

用户定义的完整性

用户完整性是针对某一具体应用的数据，为了使其满足其语义，由用户自己设定的完整性约束。

属性上的约束条件

属性上约束条件的定义

我们之前在建表的时候也经常会用到这个，比如我们使用的UNIQUE、NOT NULL以及CHECK短语就属于属性上的约束条件。

[例 5.5] 不允许取空值：在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。

要让属性不允许取空值很容易，在后面加上不允许取空值的语句NOT NULL就行了。

	CREATE TABLE SC
        (  Sno CHAR(9)  NOT NULL,	
           Cno CHAR(4)  NOT NULL,	
           Grade  SMALLINT NOT NULL,	
           PRIMARY KEY (Sno, Cno),  
           … /*如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义可以不写*/
           /*此处的前两个NOT NULL就可以省略，因为主码不为空*/
         );

[例 5.6] 列值唯一：建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

列取值唯一即加上UNIQUE约束。

CREATE TABLE DEPT
        (   Deptno  NUMERIC(2),
            Dname  CHAR(9)  UNIQUE NOT NULL，	/*要求Dname列值唯一, 并且不能取空值*/
            Location  CHAR(10),
            PRIMARY KEY (Deptno)
         );

[例5.7] CHECK短语满足条件：Student表的Ssex只允许取“男”或“女”

	CREATE TABLE Student
          ( Sno  CHAR(9) PRIMARY KEY,
            Sname CHAR(8) NOT NULL,                     
            Ssex  CHAR(2)  CHECK (Ssex IN ('男','女'))，      /*性别属性Ssex只允许取'男'或'女' */
            Sage  SMALLINT,
            Sdept  CHAR(20)
          );

若插入“男”、“女”以外的数据会拒绝写入。

[例 5.8] SC表的Grade的值应该在0和100之间。

	CREATE TABLE  SC
		(Sno CHAR(9) ,
         Cno CHAR(4),
		 Grade SMALLINT CHECK (Grade>=0 AND Grade <=100)，			   /*加入Grade取值范围是0到100的CHECK语句*/
         PRIMARY KEY (Sno,Cno),
         FOREIGN KEY (Sno) REFERENCES Student(Sno),
         FOREIGN KEY (Cno) REFERENCES Course(Cno)
        );

属性上约束条件的检查和违约处理

不满足属性约束条件的操作将被拒绝操作。

元组上的约束条件

与属性值限值相比，元组级的限制可以设置不同属性之间的取值的相互约束条件，也就是更加细致。

[例5.9] 当学生的性别是男时，其名字不能以Ms.打头

	CREATE TABLE Student
    	(Sno CHAR(9), 
         Sname CHAR(8) NOT NULL，
         Ssex CHAR(2),
         Sage SMALLINT,
         Sdept CHAR(20),
         PRIMARY KEY (Sno),
         CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')		/*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/
       );

此处的CHECK条件是或关系，只有性别是女性或者性别是男性名字不以Ms.打头的元组才能通过CHECK。

完整性约束命名子句

我们还可以使用完整性命名子句CONSTRAINT 对王振兴约束条件进行灵活的增删。

完整性约束命名子句

其格式如下：

CONSTRAINT <完整性约束条件名><完整性约束条件>

<完整性约束条件>包括NOT NULL、UNIQUE、PRIMARY KEY短语、FOREIGN KEY短语、CHECK短语等。也就是说我们可以使用这一子句完成多种完整性约束定义。

[例 5.10] 建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。

    CREATE TABLE StudentCONSTRAINT
      (   Sno  NUMERIC(6)
          CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
          Sname  CHAR(20)  
          CONSTRAINT C2 NOT NULL,
          Sage  NUMERIC(3)
          CONSTRAINT C3 CHECK (Sage < 30),
          Ssex  CHAR(2)
          CONSTRAINT C4 CHECK (Ssex IN ('男','女')),
          CONSTRAINT StudentKey PRIMARY KEY(Sno)
        );

C1,C2,C3,C4是列级约束名,让我们能够更方便的操纵这些完整性约束条件。

在实验后发现约束C2不见了，但是Sname的NOT NULL约束仍然存在。这可能是因为T-SQL中认为NULL和NOT NULL严格来说并不算约束所以没有让它单独起一个约束名吧。

[例 5.11]建立教师表TEACHER，要求每个教师的应发工资不低于3000元

应发工资是工资列Sal与扣除项Deduct之和。

	CREATE TABLE TEACHER
    	(Eno NUMERIC(4) PRIMARY KEY    /*在列级定义主码*/
         Ename CHAR(10),
         Job CHAR(8),
 		 Sal NUMERIC(7,2),
 		 Deduct NUMERIC(7,2),
 		 Deptno NUMERIC(2),
  		 CONSTRAINT TEACHERFKey FOREIGN KEY (Deptno) 		  				  
  		 REFERENCES DEPT(Deptno),
  		 CONSTRAINT C1 CHECK (Sal+Deduct>=3000) /*加入了工资条件的CHECK子句*/
        );

修改表中的完整性限制

使用ALTER TABLE语句修改表中的完整性限制。

[例 5.12] 去掉例5.10 Student表中对性别的限制

	ALTER TABLE Student 
   		 DROP CONSTRAINT C4;

简单易行的语句，使用后果然去除掉了列级属性C4。

[例 5.13] 修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40

这里采用了先去除旧的约束条件，添加新的约束条件的方法来达成题目要求的效果。

	ALTER TABLE Student
    DROP CONSTRAINT C1;
    ALTER TABLE Student
    ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999);
    ALTER TABLE Student
    DROP CONSTRAINT C3;
    ALTER TABLE Student
    ADD CONSTRAINT C3 CHECK(Sage<40);

在执行的时候出现ALTER TABLE 语句与 CHECK 约束“C1”冲突。

查阅网上资料之后发现是表中有不符合现在写入的C1的CHECK的数据，所以这个约束加不上去。解决方法就是改动这个数据至满足CHECK的范围内亦或者是直接删掉不符合的元组。

触发器

触发器(trigger)是用户定义在关系表上的一类由事件驱动的特殊过程。简单来说，设置触发器对于某一操作进行响应，那么当这一操作发生时就会触发触发器，触发器就会执行用户设定的相应操作。很像Java中的事件监听器。

定义触发器

**触发器又叫做事件-条件-动作(event-condition-action)规则。**触发器被出发后就会对规则的条件进行检查，若果通过就会执行动作，否则将拒绝执行动作。这个动作一般来说是一段SQL存储过程。
在SQL中我们一般使用CREATE TRIGGER来建立触发器，其一般格式如下：

	CREATE TRIGGER <触发器名>  
    {BEFORE|AFTER} <触发事件> ON <表名> /*对事件的响应时刻以及动作所在的表*/
    REFERENCING NEW|OLD ROW AS<变量>	/*这里设置临时变量方便对变化后的数据进行操作*/
    FOR EACH  {ROW | STATEMENT}		/*指明执行动作的频率*/
    [WHEN <触发条件>]<触发动作体>		/*触发器的触发条件*/

[例 5.21] 当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：

SC_U（Sno,Cno,Oldgrade,Newgrade）
其中Oldgrade是修改前的分数，Newgrade是修改后的分数。

	/*建表操作此处略去*/
	CREATE TRIGGER  SC_T		/*建立SC_T触发器*/
	AFTER UPDATE OF Grade ON SC
    REFERENCING
	     OLD row  AS  OldTuple,
	     NEW row AS  NewTuple	/*把旧行称为旧元组，新行称为新元组，有临时变量那味了*/
	FOR EACH ROW 	/*行级触发器，每执行一次Grade更新就执行一遍下面的规则*/
	WHEN (NewTuple.Grade >= 1.1*OldTuple.Grade)	/*触发条件*/
	    INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade)
	    VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

T-SQL Version

--建表
DROP TABLE IF EXISTS SC_U;
 
create table SC_U
(
Sno char(9) ,
Cno char(9) ,
oldgrade smallint ,
newgrade smallint ,
)
--建立触发器
create trigger SC_T
on SC
for update 
as declare @old smallint--声明变量
  declare @new smallint
  declare @Sno char(9)
  declare @Cno char(4)
if(update(Grade))
  begin 
  select @old =Grade from deleted 
  select @new =Grade from inserted
  select @Sno =Sno from deleted-为：SC，结果会错误。 
  select @Cno =Cno from deleted
  IF(@NEW>=1.1*@OLD)
  insert into SC_U(Sno,Cno,oldgrade,newgrade)
  values(@Sno, @Cno,@old,@new)
end
--测试数据
update SC
set Grade=90
where Sno='201215121' and Cno ='2'
 
select *from SC
select *from SC_U

[例5.22] 将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中

	CREATE TRIGGER Student_Count
	AFTER INSERT ON Student  	         
	REFERENCING
     	NEW TABLE AS DELTA	/*启用一个“新表”DELTA来记录新增的学生*/
	FOR EACH STATEMENT  
	      	INSERT INTO StudentInsertLog (Numbers)
   			SELECT COUNT(*) FROM DELTA

T-SQL version

--新建表StudentInsertLog，存储学生人数
CREATE TABLE StudentInsertLog
(
Numbers INT
)
--新建表StudentInsertLogUser，存储用户名和操作时间
CREATE TABLE StudentInsertLogUser
(
  UserName nchar(10),
  DateAndTime datetime
)

--新建触发器Student_Count，当插入新的学生记录时，触发器启动，自动在StudentInsertLog记录学生人数
CREATE TRIGGER Student_Count
ON Student AFTER INSERT
AS 
    INSERT INTO StudentInsertLog(Numbers)
	SELECT COUNT(*) FROM Student

--新建触发器Student_Time，当插入新的学生记录时，触发器启动，自动在StudentInsertLogUser记录用户名和操作时间
CREATE TRIGGER Student_Time
ON Student  	         
AFTER
INSERT
AS 
	declare @UserName    nchar(10)
	declare @DateTime    datetime

	select @UserName = system_user
	select @DateTime = CONVERT(datetime,GETDATE(),120) 

	INSERT INTO StudentInsertLogUser(UserName,DateAndTime)
	VALUES (@UserName,@DateTime)
--数据测试
INSERT
INTO  Student
VALUES ('201215135','王五','男',18,'CS');
SELECT * FROM Student
SELECT * FROM StudentInsertLog
SELECT * FROM StudentInsertLogUser

这里有个小纰漏，就是StudentInsertLog显示的数据是包括数据库中原有学生的总数。解决办法也很简单：在SELECT COUNT(*) FROM Student中count函数后面减去库中原有人数就行了。

[例5.23] 定义一个BEFORE行级触发器

为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”

	CREATE TRIGGER Insert_Or_Update_Sal 
    BEFORE INSERT OR UPDATE ON Teacher  
         			             /*触发事件是插入或更新操作*/
    FOR EACH ROW        /*行级触发器*/
    BEGIN                             /*定义触发动作体，是PL/SQL过程块*/
            IF (new.Job='教授') AND (new.Sal < 4000) 
             	 THEN  new.Sal :=4000;                
           	 END IF;
        END;

删除触发器

/*标准SQL*/
DROP TRIGGER <触发器名> ON <表名>;
/*T-SQL*/
DROP TRIGGER <触发器名>

存储过程与函数

[例 8.8] 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中

	CREATE OR REPLACE PROCEDURE TRANSFER(inAccount INT,outAccount  INT,amount FLOAT) 
 /*定义存储过程TRANSFER，参数为转入账户、转出账户、转账额度*/
	AS DECLARE		/*定义变量*/
	     totalDepositOut Float;
         totalDepositIn Float;
		 inAccountnum INT;
	BEGIN                         	    /*检查转出账户的余额 */     SELECT Total INTO totalDepositOut FROM Account WHERE accountnum=outAccount;
	   IF totalDepositOut IS NULL THEN  	/*如果转出账户不存在或账户中没有存款*/
	        ROLLBACK; 	   /*回滚事务*/
	        RETURN
	   END IF;
       IF totalDepositOut< amount THEN    	/*如果账户存款不足*/
	        ROLLBACK; 				/*回滚事务*/
	        RETURN
       END IF
       SELECT Accountnum INTO inAccountnum FROM Account
       WHERE accountnum=inAccount;
  	   IF inAccount IS NULL THEN  		/*如果转入账户不存在*/                        
			ROLLBACK; 	         	 		/*回滚事务*/
			RETURN;
  	   ENDIF;
  	   UPDATE Account SET total=total-amount WHERE accountnum=outAccount; /* 修改转出账户余额，减去转出额 */
  	   UPDATE Account SET total=total+amount WHERE accountnum=inAccount; /* 修改转入账户余额，增加转入额 */
  	   COMMIT;                       	/* 提交转账事务 */
	END;

T-SQL Version

--建立新表Account，并写入两个用户
DROP TABLE IF EXISTS Account;

CREATE TABLE Account
(
accountnum CHAR(3),	-- 账户编号
total FLOAT		-- 账户余额
);

INSERT INTO Account VALUES(101,50);
INSERT INTO Account VALUES(102,100);

SELECT * FROM Account
--建立存储过程
IF (exists (select * from sys.objects where name = 'Proc_TRANSFER'))
    DROP PROCEDURE Proc_TRANSFER
GO
CREATE PROCEDURE Proc_TRANSFER 
@inAccount INT,@outAccount  INT,@amount FLOAT
 /*定义存储过程TRANSFER，参数为转入账户、转出账户、转账额度*/
AS
BEGIN TRANSACTION TRANS   
   	DECLARE		/*定义变量*/
	@totalDepositOut Float,
	@totalDepositIn Float,
	@inAccountnum INT;
	 /*检查转出账户的余额 */     
	SELECT @totalDepositOut = total FROM Account	WHERE accountnum = @outAccount;
	/*如果转出账户不存在或账户中没有存款*/
	IF @totalDepositOut IS NULL               	   
		BEGIN
			PRINT '转出账户不存在或账户中没有存款'
			ROLLBACK TRANSACTION TRANS; 	   /*回滚事务*/
			RETURN;
		END;
	/*如果账户存款不足*/
	IF @totalDepositOut < @amount     	
		BEGIN
			PRINT '账户存款不足'
			ROLLBACK TRANSACTION TRANS; 				/*回滚事务*/
			RETURN;
		END
	/*检查转入账户的状态 */  
	SELECT @inAccountnum = accountnum  FROM Account	WHERE accountnum = @inAccount;
	/*如果转入账户不存在*/ 
	IF @inAccountnum IS NULL   		                       
		BEGIN
			PRINT '转入账户不存在'
			ROLLBACK TRANSACTION TRANS; 	         	 		/*回滚事务*/
			RETURN;
		END;
	/*如果条件都没有异常，开始转账。*/ 
	BEGIN
		UPDATE Account SET total = total - @amount	WHERE	accountnum = @outAccount; /* 修改转出账户余额，减去转出额 */
		UPDATE Account SET total = total + @amount	WHERE   accountnum = @inAccount; /* 修改转入账户余额，增加转入额 */
		PRINT '转账完成，请取走银行卡'
		COMMIT TRANSACTION TRANS;                       	/* 提交转账事务 */
		RETURN;
	END
--执行存储过程
EXEC	Proc_TRANSFER
		@inAccount = 101,	--转入账户
		@outAccount = 102,	--转出账户
		@amount = 50		--转出金额

SELECT * FROM Account

此处T-SQL建立的存储过程，正常情况下汇款会正常进行，若是出现异常情况如钱不够、空账号等，数据库将会禁止此次操作，没有任何影响。

[例 8.9] 从账户01003815868转10000元到01003813828账户中

	CALL PROCEDURE       
		TRANSFER(01003813828,01003815868,10000);

参考文献：
[1]萨师煊，王珊，数据库系统概论.5版.北京:高等教育出版社,2014.
[2]David老师的PPT.
[3]HBU_David的博客

FDR_Enterprise

关注

7
点赞
踩
30

收藏

觉得还不错? 一键收藏
1
评论
数据库完整性及相关SQL语句

SQL练习8 - CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION
复制链接

扫一扫

数据库完整性及相关SQL语句

文章目录

数据库完整性SQL语句

实体完整性

定义实体完整性

[例 5.1] 将Student表中的Sno属性定义为码

[例 5.2] 将SC表中的Sno，Cno属性组定义为码

实体完整性检查和违约处理

参照完整性

定义参照完整性

[例 5.3] 定义SC中的参照完整性

参照完整性检查和违约处理

[例 5.4] 显式说明参照完整性的违约处理示例

用户定义的完整性

属性上的约束条件

属性上约束条件的定义

[例 5.5] 不允许取空值：在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。

[例 5.6] 列值唯一：建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

[例5.7] CHECK短语满足条件：Student表的Ssex只允许取“男”或“女”

[例 5.8] SC表的Grade的值应该在0和100之间。

属性上约束条件的检查和违约处理

元组上的约束条件

[例5.9] 当学生的性别是男时，其名字不能以Ms.打头

完整性约束命名子句

完整性约束命名子句

[例 5.10] 建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。

[例 5.11]建立教师表TEACHER，要求每个教师的应发工资不低于3000元

修改表中的完整性限制

[例 5.12] 去掉例5.10 Student表中对性别的限制

[例 5.13] 修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40

触发器

定义触发器

[例 5.21] 当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：

[例5.22] 将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中

[例5.23] 定义一个BEFORE行级触发器

删除触发器

存储过程与函数

[例 8.8] 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中

[例 8.9] 从账户01003815868转10000元到01003813828账户中

“相关推荐”对你有帮助么？