完整性约束

完整性定义

实体完整性

实体完整性要求每个表都要有自己的主键。

定义主键有两种方式：

列级定义

在定义表字段时，在该字段后定义PRIMARY KEY。

sno INT PRIMARY KEY,
表级定义

在定义好表各个字段后，定义主键。

PRIMARY KEY(sid),

列级定义只能定义一个主键，表级定义可以定义联合主键(2个以上字段构成主键)，只能在表级定义。

完整性检查我和违约处理

检查主键值是否唯一，若不唯一，拒绝操作。
检查主键各个属性是否为空，若空，拒绝操作。

参照完整性

参照完整性不允许引用不存在的实体。保证数据一致性。

参照完整性只能在表级定义。FOREGIN KEY (sno) REFERENCES Student(sno)。

完整性检查和违约处理

被参照表	参照表	违约处理
可能破坏参照完整性	插入元组	拒绝
可能破坏参照完整性	修改外键值	拒绝
删除元组	可能破坏参照完整性	拒绝/级联/设为空值
修改主键值	可能破坏参照完整性	拒绝/级联/设为空值

处理方法：

NO ACTION：拒绝执行。FOREGIN KEY (sno) REFERENCES Student(sno) ON UPDATE NO ACTION
CASCADE：级联执行。FOREGIN KEY (sno) REFERENCES Student(sno) ON UPDATE CASCADE
SET NULL：设为空值。FOREGIN KEY (sno) REFERENCES Student(sno) ON UPDATE SET NULL

用户定义完整性

针对某一具体应用的数据必须满足的语义要求。

属性约束

属列级定义。

列值非空(NOT NULL)：主键一定非空。
列值唯一(UNIQUE)：唯一隐含着非空条件。
数据检查(CHECK)：检查数据是否符合规则。

元组约束

属表级定义。

一般用CHECK语句来检查数据是否冲突。

CHECK (animal = 'is' OR name NOT LIKE 'an.%') //规定不是动物时不以an.开头。

约束方法

一般可以有一些更复杂方法来更方便地约束完整性。

完整性约束子句

CONSTRAINT <constraintName> <constraintCondition>

约束条件包括NOT NULL(MySQL不支持)、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK等。

约束子句如果有一个名字更便于管理和纠错。支持列级和表级约束。

-- 创建表时直接使用约束子句
DROP TABLE IF
	EXISTS student;
CREATE TABLE Student (
	sname VARCHAR ( 20 ),
	sid INT 
	CONSTRAINT IdRange CHECK ( sid BETWEEN 10000 AND 99999 ),
	ssex VARCHAR ( 1 ) 
	CONSTRAINT SexValid CHECK (ssex IN ( 'F', 'M' )),
	sage INT DEFAULT 18
	CONSTRAINT AgeRange CHECK (sage < 30),
CONSTRAINT StuKey PRIMARY KEY ( sid ),
CONSTRAINT NameNotNil CHECK (sname IS NOT NULL AND sname <> '')
);
-- 删除约束子句
ALTER TABLE student DROP CONSTRAINT NameNotNil;
-- 添加约束子句
ALTER TABLE student ADD CONSTRAINT NotNil CHECK (sname IS NOT NULL AND sname <> '');

触发器

/* 要包含监视对象，监视事件，触发时机，触发事件。*/
CREATE TRIGGER triggerName 
{BEFOR|AFTER} {INSERT|UPDATE|DELETE} ON tableName
FOR EACH ROW	-- 这句话是固定格式
BEGIN
	excuteSQL;	-- 其中NewRow,OldRow可以使用new,old来代指，INSERT,UPDATE,DELETE触发器根据其作用含有new或old。也可以进行条件判断。
END
-- 删除触发器
	-- 触发器不可修改，只能删除重建
DROP TRIGGER triggerName;
-- 查看触发器
SHOW TRIGGERS;	-- 列出所有触发器
SHOW CREATE TRIGGER triggerName;	-- 查看指定触发器

特点

如果在触发器中出现错误，那么前面的已经执行的操作也会全部清空。
mysql触发器不能对同一张表进行修改操作。即MySQL 的触发器中不能对本表进行 insert、update 和 delete 操作，否则会报错。

优点：

触发器可以通过数据库中的关联表实现级联更改。
可以保证数据安全，进行安全校验。

缺点：

过分依赖触发器，影响数据库的结构，增加数据库的维护成本。

📌Example

/*手机店铺批发手机，shop存储手机品牌和型号，sell是出售记录。每次售出后都shop表也要随之变化。*/
-- 触发器
CREATE TRIGGER SellTrigger 
AFTER INSERT ON sell
FOR EACH ROW
BEGIN
	SELECT pnum FROM shop WHERE pname=new.sell_name INTO @num;
	
	IF new.sell_num < @num THEN
		UPDATE shop SET pnum=pnum-new.sell_num WHERE pname=new.sell_name;
	ELSE 
		SET @msg = '库存不足';
		SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = @msg;
	END IF;
END

-- 测试语句
INSERT INTO sell (sell_name,sell_num,sell_date) VALUES ('Apple', 5 ,NOW())

-- 表数据
	-- shop
	pname	pnum
	Apple	15		-- 源数据为20
	XiaoMi	30
	-- sell
	sell_num	sell_name	sell_date
	5			Apple		2020-05-07 23:24:10
-- 测试语句
INSERT INTO sell (sell_name,sell_num,sell_date) VALUES ('Apple', 25 ,NOW())
	-- 返回错误
	> INSERT INTO sell (sell_name,sell_num,sell_date) VALUES ('Apple', 25 ,NOW())
	> 1644 - 库存不足

this

Falle-Angle

MySQL完整性约束