数据库完整性深入解析-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_40209351/article/details/106710401

数据库完整性：防止不合语义、不正确的数据进入数据库;
数据库安全性：防范非法用户对数据库的入侵、非法存取数据库数据。
5.1实体完整性 PRIMARY KEY
实体完整性定义：单属性——列级、表级约束，多属性——仅表级约束
列级定义主码

CREATE TABLE STUDENT
(SNO CHAR(9)PRIMARY KEY,/*列级定义主码*/
SNAME CHAR(20) NOT NULL,
SSEX CHAR(2),
SAGE SMALLINT,
SDEPT CHAR(20)
);

表级定义主码

CREATE TABLE SC
(SNO CHAR(9) NOT NULL,
CNO CHAR(4) NOT NULL,
GRADE SMALLINT,
PRIMARY KEY(SNO,CNO)/*表级定义主码*/
);

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

违约处理

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

5.3用户定义完整性 NOT NULL、UNIQUE、CHECK
列值非空NOT NULL
列值唯一UNIQUE
列值是否满足布尔表达式CHECK

列值非空

CREATE TABLE SC
(SNO CHAR(9) NOT NULL,
CNO CHAR(4) NOT NULL,
GRADE SMALLINT,
PRIMARY KEY(SNO,CNO)/*表级定义实体完整性，其实已经隐含了SNO,CNO不允许取空值
)

列值唯一
略

约束
属性上的约束

CREATE TABLE STUDENT
(SNO CHAR(9) PRIMARY KEY,
SNAME CHAR(8) NOT NULL,
SSEX CHAR(2) CHECK(SSEX IN ('男','女')),
SAGE SMALLINT CHECK(SAGE>=0 AND SAGE<=150),
SDEPT CHAR(20)
);

元组上的约束条件
元组级限制可设置不同属性之间的取值的相互约束

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 LIEK 'Ms.%')
）；

5.4完整性约束命名子句(其实就是给三种完整性约束命名)
CONSTRAINT,对完整性约束条件命名
语法：CONSTRAINT<完整性约束条件名>[PRIMARY KEY短语|FOREIGN KEY短语|CHECK短语]

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 EMPFKEY FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
CONSTRAINT C1 CHECK (SAL+DEDUCT>=3000)
);

修改表中完整性限制

ALTER TABLE TEACHER
DROP CONSTRAINT C1;
ALTER TABLE STUDENT
ADD CONSTRAINT C1 CHECK(SAL+DEDUCT>=4000);

*5.5域中的完整性限制
SQL支持域概念
建立性别域，并声明性别域的取值范围

CREATE DOMAIN GENDERDOMAIN CHAR(2)
CHECK (VALUE IN ('男','女'));

5.6触发器
用户定义在关系表上的一类由事件驱动的特殊过程，定义后用户操作由服务器自动激活相应的触发器
语法
CREATE TRIGGER <触发器名>
{BEFORE|AFTER} <触发事件> ON <表名>
FOR EACH{ROW|STATEMENT}
[WHEN <触发条件>]
<触发动作体>

1、表的拥有者才可在表上创建触发器
2、触发器名在同一模式下唯一，触发器名和<表名>必须在同一模式下
3、触发条件：触发器被激活时，若触发条件为真，则执行触发动作体；若省略WHEN触发条件，则触发器激活后立即执行
4.触发动作体：匿名PL/SQL过程块对已创建存储过程的调用

NEW/OLD(old!!)引用	匿名PL/SQL过程块	对已创建存储过程的调用
行级触发器	可	可
语句级触发器	不可	不可

BEFORE行级触发器

CREATE TRIGGER Insert_Or_Update_Sal
  BEFORE ISNERT OR UPDATE ON Teacehr
  FOR EACH ROW
  AS BEGIN
     IF (new.Job='教授') AND (new.sal<4000) THEN
            new.sal=4000;
      END IF;
  END;

AFTER行级触发器
教师表工资变化后在工资变化表增加记录

CREATE TABLE Sal_log
  (Eno NUMERIC(4) REFERENCES teacher(Eno),
  Sal NUMERIC(7,2),
  Username char(10),
  Date TIMESTAMP
  );
CREATE TRIGGER Insert_Sal
  AFTER INSERT ON Teacher
  FOR EACH ROW
  AS BEGIN
    INSERT INTO Sal_log VALUES(
            new.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP);/*为啥不用new.username*/
    END;
CREATE TRIGGER Update_Sal
  AFTER UPDATE ON Teacher
  FOR EACH ROW
  AS BEGIN
    IF (new.Sal<>old) THEN INSERT INTO Sal_log VALUES(
          new.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP);
    END IF;
  END;