21/7/29 读书笔记 数据库完整性约束

21/7/29 读书笔记

目录

数据库系统导论 数据库完整性

数据库的完整性是指数据的正确性和相容性：

  • 正确性：数据中的对象应当能够正确描述现实世界。比如人的身高不应该超过3m。
  • 相容性：数据中的对象在不同关系表中表现出的数据应当符合逻辑。比如同一个人不能在两张关系表中具有不同的性别。

SQL在数据定义语言（DDL）语句中提供了定义完整性约束条件的机制，并将其作为数据库模式的一部分放在数据字典中。

在关系数据库中，数据库的完整性主要包括：实体完整性、参照完整性、用户定义的完整性。

实体完整性

实体，对应数据库中的一个对象。我们定义了主码来一一对应于一个实体，因此保证实体的完整性就是保证主码符合一定条件：

  • 主码值必须唯一
  • 主码的各个属性必须都不为空

SQL提供两种定义主码的方式：

CREATE TABLE SCD
(
Sno CHAR(9),
Cno CHAR(9) PRIMARY KEY, # 列级约束条件，只将Cno指定为主码
Grad SMALLINT,
PRIMARY KEY (Sno,Cno) # 表级约束条件，如果需要将Cno与Sno同时定义为主码，则只能采取该方式
);

参照完整性

参照，是指多个不同关系表中利用外码的方式相互联系。由于外码与主码之间存在相互对应关系，使得外码需要满足：

  • 外码属性不为空
  • 主码所在表（被参照表）中应当存在外码相对应的值

SQL提供表级约束的方式定义外码，并提供了违约处理定义：

CREATE TABLE SCD
(
Sno CHAR(9),
Cno CHAR(9),
FOREIGN KEY (Sno) REFERENCES Student(Sno)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
FOREIGN KEY (Sno) REFERENCES Student(Sno)
    ON UPDATE NO ACTION
    ON DELETE No ACTION
);

违约情形包括：

  • 向被参考表进行删除DELETE或修改UPDATE，导致被参考的值与参考表的外码不一致
    • 按照建立时指定的约束执行相应操作
      • CASCADE 级联：所有基于该被参考值的外码所在元组都会被删除或修改，以满足参照完整性约束
      • NO ACTION 拒绝：数据库将拒绝该操作，以保持参照完整性约束
  • 向参考表进行插入INSERT或修改UPDATE，导致参考表的外码在被参考表中没有对应
    • 数据库系统将拒绝该操作

当被参考值改变，其实还可以将基于该参考值的外码设置为空来维持参照完整性约束

一般来说，外码不应该为空，因为外码在参考表中也能唯一标识一个元组

用户定义的完整性

用户可以根据需求对数据进行约束，分为：

  • 属性上的约束条件：只约束该列的属性值满足一定条件，不涉及多条属性，定义为列级约束条件
    • 包括UNIQUE、NOT NULL以及CHECK短语
  • 元组上的约束条件：约束多列的属性之间的关联关系，定义为表级约束条件
CREATE TABLE SC
(
Sno CHAR(9) UNIQUE,
Sname CHAR(10) NOT NULL,
Grade SMALLINT CHECK (Grade>=0 AND Grade<=100), # 列级约束条件
Plus SMALLINT,
CHECK (Plus+Grade<100 AND Plus<10) # 表级约束条件
);

为完整性约束条件起个名字吧

以上介绍的约束条件都是在定义表时定义。但是当我们需要取消或新增一个约束条件时应该怎么做呢？难道要重建一整张表吗？SQL提供了完整性约束命名子句，使得我们能用一个名字唯一标识一个约束，从而使得我们能够更好地进行约束的控制。

CREATE TABLE SC
(
Sno CHAR(9) 
    CONSTRAINT SC_Sno_unique UNIQUE,
Sname CHAR(10) 
    CONSTRAINT SC_Sname_notnull NOT NULL,
Grade SMALLINT 
    CONSTRAINT SC_Grade_check1 CHECK (Grade>=0 AND Grade<=100), #利用CONSTRAINT子句为所有约束命名
Plus SMALLINT,
CONSTRAINT SC_check1 CHECK (Plus+Grade<100 AND Plus<10) # 注意命名需要在全局具有唯一性
);

ALTER TABLE SC
DROP CONSTRAINT SC_Grade_check1; # 删除约束

ALTER TABLE SC
ADD CONSTRAINT SC_Grade_check1 CHECK (Grade>=60 AND Grade<=100) # 新增约束，无需指定列。由此可见约束都是以表级约束表示

域约束

SQL对于域的概念基于数据类型，其自带的数据类型可能无法满足我们对域的需求。比如我们可能需要某种数据类型来表示性别，而在不同的情况下对于性别的定义也不一样。SQL允许我们自定义一个域并且在该域上设置约束。

CREATE DOMAIN Sex CHAR(6)
CONSTRAINT Sex_valuecheck CHECK(VALUE IN ('man','woman'));

ALTER DOMAIN Sex
DROP CONSTRAINT Sex_valuecheck;

ALTER DOMAIN Sex
ADD CONSTRAINT Sex_valuecheck CHECK(VALUE IN ('男','女','男转女','女转男','中性'));

断言

当我们执行某个数据库操作后，我们希望保证多个关系表之间维持某种关系不变。SQL定义了断言来描述多张关系表之间需要满足的约束，而且任何对断言所涉及关系进行的操作都会触发断言检查，如果破坏了断言则拒绝执行。

CREATE ASSERTION SC_Course
CHECK (60 >= (SELECT COUNT(*) FROM Course,SC WHERE Course.Cno=SC.Cno AND Course.Cname='OS'));
# 检查OS课程选课人数不能大于60

触发器

之前介绍的各种约束，触发后数据库系统执行的操作都是较为简单的，比如拒绝执行或者级联修改，而且这些行为本身是无法由客户定制的。触发器是一种定义在关系表上的，能够触发用户自定义行为的一种机制。

具体来说，当特定的系统事件发生，将对触发器规则进行检查，检查为真则触发操作。操作是由用户定义的动作体，其通常表示为一系列用语句描述的存取过程。在SQL中，我们可以指定触发器激活是在执行触发事件前还是后，还可以设置引用变量别名、定义触发器执行类型、设置触发条件。

CREATE TRIGGER <触发器名>
{BEFORE|AFTER} <触发事件> ON <基本表名>
REFERENCING NEW|OLD ROW|TABLE AS <变量名>
FOR EACH {ROW|STATEMENT}
[WHEN <触发条件>] <触发动作体>

接下来我们用样例一一介绍：

CREATE TRIGGER SC_trigger1
BEFORE INSERT OR UPDATE ON SC # 指定为语句执行前
REFERENCING NEW ROW AS newtuple # 当指定为每行触发时，REFERENCING的对象为NEW或OLD ROW
FOR EACH ROW # 指定为每行执行一次触发条件检查
BEGIN
	IF(newtuple.Grade<60)
		THEN newtuple.Grade=60;
	END IF;
END; # 采用PL/SQL过程块描述动作体

CREATE TRIGGER Student_count
AFTER INSERT ON Student # 指定为语句执行后
REFERENCING NEW TABLE AS newtable # 当指定为每条语句触发时，REFERENCING的对象是NEW或OLD TABLE
FOR EACH STATEMENT # 指定为每条语句执行一次触发条件检测
	INSERT INTO StudentLog(Number)
	SELECT COUNT(*) FROM newtable; # 采用SQL语句描述动作体
posted @ 2021-07-29 09:48  neumy  阅读(437)  评论(0)    收藏  举报