当前位置： 代码迷 >> 综合 >> 数据库作业13：CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION
  详细解决方案

数据库作业13：CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION

热度：85   发布时间：2023-12-18 15:25:51.0

第五章：数据库完整性

  • 5.1 实体完整性
    • 5.1.1 定义实体完整性
  • 5.2 参照完整性
    • 5.2.1 定义参照完整性
    • 5.2.2 参照完整性检查和违约处理
  • 5.3 用户定义的完整性
    • 5.3.1 属性上的约束条件
      • 属性上约束条件的定义
      • 属性上约束条件的检查和违约处理
    • 5.3.2 元组上的约束条件
      • 元组上约束条件的定义
      • 元组上约束条件的检查和违约处理
  • 5.4 完整性约束命名子句
      • 完整性约束命名子句
      • 修改表中的完整性限制
  • 5.7 触发器
    • 5.7.1 定义触发器
    • 5.7.2 激活触发器
    • 5.7.3 删除触发器
  • 存储过程（应用题）

5.1 实体完整性

5.1.1 定义实体完整性

关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义。对单属性构成的码有两种说明方法，一种是定义为列级约束条件，另一种是定义为表级约束条件。对多个属性构成的码只有一种说明方法，即定义为表级约束条件。

【例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) /*只能在表级定义主码*/)

5.2 参照完整性

5.2.1 定义参照完整性

关系模型的参照完整性在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。
例如，关系SC中一个元组表示一个学生的选修的某门课程的成绩，（Sno，Cno）是主码。Sno，Cno分别参照Student表的主码和Course表的主码

【例5.3】定义SC中的参照完整性

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

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

在这里插入图片描述

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

CREATE TABLE SC(Sno CHAR(9) NOT NULL,Cno CHAR(4) NOT NULL,Grade SMALLINT,PRIMARY KEY(Sno,Cno), /*在表级定义实体完整性,Sno、Cno都不为空值*/FOREIGN KEY(Sno) REFERENCES Student(Sno)/*在表级定义参照完整性*/ON DELETE CASCADE  /*当删除Student表中的元组时，级联删除SC表中对应的元组*/ON UPDATE CASCADE, /*当更新Student表中的Sno时，级联更新SC表中的相应元组*/FOREIGN KEY(Cno) REFERENCES Course(Cno)ON DELETE NO ACTION   /*当删除Course表中的元组造成与SC表不一致的时候，拒绝删除*/ON UPDATE CASCADE     /*当更新Course表中的Cno时，级联更新SC表中的相应元组*/)

5.3 用户定义的完整性

5.3.1 属性上的约束条件

属性上约束条件的定义

在CREATE TABLE中定义属性的同时，可以根据应用要求定义属性上的约束条件，即属性值限制，包括：
列值为非空（NOT NULL）
列值唯一（UNIQUE）
检查列值是否满足一个条件表达式（CHECK短语）

（1）不允许取空值
【例5.5】在定义SC表时，说明Sno、Cno、Grade属性不允许为空值

CREATE TABLE SC(Sno CHAR(9) NOT NULL,Cno CHAR(4) NOT NULL,Grade SMALLINT NOT NULL,PRIMARY KEY(Sno,Cno), /*在表级定义实体完整性,Sno、Cno都不为空值*/)

（2）列值唯一
【例5.6】建立部门表DEPT，要求部门名称Dname列值取值唯一，部门编号Deptno列为主码

CREATE TABLE DEPT(Deptno NUMERIC(2),Dname CHAR(9)UNIQUE NOT NULL,Location CHAR(10),PRIMARY KEY(Deptno), /*在表级定义实体完整性,Sno、Cno都不为空值*/)

（3）用CHECK短语指定列值应该满足的条件
【例5.7】Student表的Ssex只允许取“男”或“女”

CREATE TABLE Student(Sno CHAR(9) PRIMARY KEY,Sname CHAR(8)NOT NULL,Ssex CHAR(2)CHECK(Ssex IN('男','女')),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),PRIMARY KEY(Sno,Cno),FOREIGN KEY(Sno) REFERENCES Student(Sno),FOREIGN KEY(Cno) REFERENCES Course(Cno),)

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

在这里插入图片描述
当往表中插入元组或修改属性的值时，关系数据库管理系统将检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行。
检查这东西是系统自动的，检查出来不行就不让执行，简单简单。

5.3.2 元组上的约束条件

元组上约束条件的定义

与属性上约束条件的定义类似，在CREATE TABLE语句中可以用CHECK语句定义元组上的约束条件，即元组级的限制，同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件

【例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.%'))

元组上约束条件的检查和违约处理

当往表中插入元组或修改属性的值时，关系数据库管理系统将检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行。
（好熟悉啊emmm）

5.4 完整性约束命名子句

完整性约束命名子句

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

完整性约束条件包括NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK短语

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

CREATE TABLE Student(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))

【例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 TEACHERKey FOREIGN KEY(Deptno)REFERENCES DEPT(Deptno),CONSTRAINT C1 CHECK(Sal+Deduct>=3000))

修改表中的完整性限制

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

【例5.12】去掉例 5.10 Student表中对性别的限制

ALTER TABLE StudentDROP CONSTRAINT C4

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

ALTER TABLE StudentDROP CONSTRAINT C1
ALTER TABLE StudentADD CONSTRAINT C1 CHECK(Sno BETWEEN 900000 AND 999999)
ALTER TABLE StudentDROP CONSTRAINT C3
ALTER TABLE StudentADD CONSTRAINT C3 CHECK(Sage<40)

5.7 触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊过程

5.7.1 定义触发器

触发器也叫做事件-条件-动作规则。
当特定的系统事件（如对一个表的增、删、改操作，事物的结束等）发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。规则中的动作体可以很复杂，可以涉及其他表和其他数据库对象，通常是一段SQL存储过程
SQL使用CREATE TRIGGER命令建立触发器，其格式为：
CREATE TRIGGER<触发器名> //每当触发事件发生的时候，该触发器被激活
{BEFORE|AFTER}<触发事件>ON<表名> //指明触发器激活的时间是执行触发事件前或后
REFERENCING NEW|OLD ROW AS<变量> //REFERENCING指出引用的变量
FOR EACH{ROW|STATEMENT} //定义触发器的类型，指明动作体执行的频率
[WHEN<触发条件>]<触发动作体> //仅当触发条件为真时才执行触发动作体

【例5.21】当对表SC的Grade属性进行修改时，若分数增加了10%，则将此次操作记录到另一个表SC_U(Sno,Cno,Oldgrade,Newgrade)中，其中Oldgrade是修改前的分数，Newgrade是修改后的分数

CREATE TABLE SC_U(Sno CHAR(9) PRIMARY KEY,  /*在列级定义主码*/Cno CHAR(9),Oldgrade SMALLINT,Newgrade SMALLINT)

标准SQL

CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC
REFERENCINGOLDROW AS OldTuple,NEWROW AS NewTuple
FOR EACH ROW
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应该是另一种写法
如下

CREATE TRIGGER SC_T
ON SC
FOR UPDATE
ASdeclare @OLD SMALLINTdeclare @NEW SMALLINTdeclare @SNO CHAR(9)declare @CNO CHAR(4)
IF(UPDATE(Grade))BEGINselect @OLD =Grade FROM DELETEDselect @NEW =Grade FROM INSERTEDselect @SNO =Sno FROM DELETEDselect @CNO =Cno FROM DELETEDIF(@NEW>=1.1*@OLD)INSERT INTO SC_U(Sno,Cno,Oldgrade,Newgrade)VALUES (@SNO,@CNO,@OLD,@NEW)END

【例5.22】将每次对表Student的插入操作所增加的学生个数记录到表Student-InsertLog中

1、新建表StudentInsertLog，存储学生人数

CREATE TABLE StudentInsertLog(Numbers INT

2、新建表StudentInsertLogUser存储用户名和操作时间

CREATE TABLE StudentInsertLogUser(UserName NCHAR(10),DateAndTime datetime

3、新建触发器Student_Count,插入新的学生记录的时候，触发器启动，自动在StudentInsertLog里记录学生的人数

CREATE TRIGGER Student_Count
ON Student
AFTER 
INSERT
ASINSERT INTO StudentInsertLog(Numbers)SELECT COUNT(*) FROM Student

4、新建触发器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)
end

【例5.23】定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不低于4000元，如果低于4000元，自动改为4000元”

首先我们需要先建立一张Teacher表，并且为Teacher表添加数据

CREATE TABLE Teacher(Tno CHAR(9),Tname CHAR(9),Job CHAR(9),Sal SMALLINT
)INSERT
INTO Teacher(Tno,Tname,Job,Sal)
VALUES('201201','温宁','讲师',3000)
INSERT
INTO Teacher(Tno,Tname,Job,Sal)
VALUES('201202','焦炎','教授',3000)
INSERT
INTO Teacher(Tno,Tname,Job,Sal)
VALUES('201203','王超','教授',5000)SELECT *
FROM Teacher

在这里插入图片描述

CREATE TRIGGER Insert_Or_Update_Sal
ON Teacher
FOR UPDATE,INSERT
ASIF UPDATE(Sal)BEGINdeclare @TNO CHAR(9)declare @TNAME CHAR(9)declare @JOB CHAR(9)declare @SAL SMALLINT
select @SAL = Sal FROM INSERTEDselect @TNO =Tno FROM Teacherselect @TNAME =Tname FROM Teacherselect @JOB =Job FROM TeacherIF(@SAL<4000 AND @JOB='教授')UPDATE TeacherSET SAL=4000WHERE Sal<4000 AND Job='教授'
END

5.7.2 激活触发器

触发器的执行是由触发事件激活，并由数据库服务器自动执行的。一个表上可能有多个触发器，激活顺序如下
（1）执行该表上的BEFORE触发器
（2）激活触发器的SQL语句
（3）执行该表上的AFTER触发器
对于一个表上的多个BEFORE（AFTER）触发器，遵循“谁先创建谁先触发”的原则

5.7.3 删除触发器

DROP TRIGGER <触发器名> ON <表名>

存储过程（应用题）

【例8.8】利用存储过程实现下面的应用：从账户1转指定数额的款项到账户2中，假设账户关系为Account（Accountnum，Total）
1、立新表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

2、建立存储过程

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                   BEGINPRINT '转出账户不存在或账户中没有存款'ROLLBACK TRANSACTION TRANS;     /*回滚事务*/RETURN;END;
/*如果账户存款不足*/IF @totalDepositOut < @amount      BEGINPRINT '账户存款不足'ROLLBACK TRANSACTION TRANS;     /*回滚事务*/RETURN;END
/*检查转入账户的状态 */  
SELECT @inAccountnum = accountnum  FROM Account	WHERE accountnum = @inAccount;
/*如果转入账户不存在*/ 
IF @inAccountnum IS NULL                            BEGINPRINT '转入账户不存在'ROLLBACK TRANSACTION TRANS;               /*回滚事务*/RETURN;END;
/*如果条件都没有异常，开始转账。*/ BEGINUPDATE Account SET total = total - @amount WHERE accountnum = @outAccount; /* 修改转出账户余额，减去转出额 */UPDATE Account SET total = total + @amount WHERE   accountnum = @inAccount; /* 修改转入账户余额，增加转入额 */PRINT '转账完成，请取走银行卡'
COMMIT TRANSACTION TRANS;                       	/* 提交转账事务 */RETURN;END

3、执行存储过程

EXEC	Proc_TRANSFER@inAccount = 101,	--转入账户@outAccount = 102,	--转出账户@amount = 50		--转出金额SELECT * FROM Account

4、测试
（1）

EXEC	Proc_TRANSFER@inAccount = 101,	--转入账户@outAccount = 102,	--转出账户@amount = 50		--转出金额SELECT * FROM Account

在这里插入图片描述
（2）

EXEC	Proc_TRANSFER@inAccount = 101,	--转入账户@outAccount = 102,	--转出账户@amount = 100		--转出金额

在这里插入图片描述
（3）

EXEC	Proc_TRANSFER@inAccount = 100,	--转入账户@outAccount = 102,	--转出账户@amount = 50		--转出金额

在这里插入图片描述
【例8.9】从账户01003815868 转10000元到01003813828账户中

SELECT * FROM AccountINSERT INTO Account VALUES(01003815868,20000)
INSERT INTO Account VALUES(01003813828,10000)
EXEC Proc_TRANSFER@inAccount = 01003813828, --转入账户@outAccount = 01003815868, --转出账户@amount = 10000  --转出金额SELECT * FROM Account

在这里插入图片描述

ps：不是很难理解，但是很难记的。

  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.