数据库笔记10---数据库完整性（哈工大）

我就是我 2022-12-17 08:43 168阅读 0赞

# 数据库完整性的概念及分类 #

## 怎样保证数据库完整性 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center]

## 完整性约束条件的一般形式 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 1]

## 数据库完整性的分类 ##

### 按约束对象分类 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 2]

### 按约束来源分类 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 3]

### 按约束状态分类 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 4]

# 利用SQL语言实现数据库完整性 #

## SQL支持的完整性约束 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 5]

## SQL实现静态约束方法：Create Table ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 6]

### 列约束 ###

列约束针对单一列进行约束  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 7]  
REFERENCES：作外键

示例 (蓝色部分是列约束)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 8]

### 表约束 ###

形式和列约束基本相似  
表约束是针对多列或元组的值进行约束  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 9]  
示例 (蓝色部分是表约束)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 10]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 11]  
where后的条件也可以作为约束，上图示例中的约束相当于S\#,C\#作外键

### 修改约束 ###

## SQL实现静态约束方法：断言ASSERSION ##

断言使用比较方便但会增加数据库维护的负担  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 14]

示例  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 15]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 16]

## SQL实现动态约束方法：触发器Trigger ##

### 事件 ###

### 示例 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 21]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 22]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 23]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 24]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 25]  
[=：是防sql注入][sql]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 26]

**以上示例均符合完整性约束规则 Integrity Constraint ::= ( O , P , A , R )**  
**O：约束的对象  
P：约束的规则  
A：触发条件  
R：响应的动作**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221122/91bdcb63d75542b0b35502b809cc2afd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221122/a84c877c3272451499faf6539de896e9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221122/9e6e1ef24e154d749344b6353c22ceae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221122/49eb6d57ba39456f82d6c920749a465d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221122/fb64cbad31354807bfe7ccdd1dc7ccb1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221122/77c441460ab74996b75385802b847028.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221122/43756dcf9a1249a9ae79f3ca89e2ee14.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221122/c3ac79ede4674806ab3c43cb02550abb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221122/eaff8d5c0e694178b1d347d153b43139.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20221122/dfd0a1c5fc3c4c9a82c707bf6597abd3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 10]: /images/20221122/a76f0a7b618344bdbfbe881bb61c5d71.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 11]: /images/20221122/2667b8efc5794ed9860dd77697c99be5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 12]: /images/20221122/7862a02fac2644d8b9a38a9abe6e4988.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 13]: /images/20221122/b07ea5232bb7469daa0495be83a3165f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 14]: /images/20221122/8fd4f7e25f114f0e8214b0f7a91ffef4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 15]: /images/20221122/03d9adf037e6406191d571431f1a114b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 16]: /images/20221122/8a8fccabb62b461aa8aa76f7fb351150.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 17]: /images/20221122/52b63f7d52544c4f9f623b7630efa09f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 18]: /images/20221122/0471dc93a1f0444393f5d9f1f553b116.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 19]: /images/20221122/295df2e8b80a4ced9e30e6144029f9bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 20]: /images/20221122/1e06fde8621645408674c20a2bf3cf69.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 21]: /images/20221122/9c5141eda46b44318d90248376cc6c3a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 22]: /images/20221122/902bcc7e0b0f4c928eb58339d0979299.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 23]: /images/20221122/64213ec0d2274c909332d4f3ae1ca712.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 24]: /images/20221122/36af63175b8a4cc8b73196b9ce194839.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 25]: /images/20221122/f0131cf5bb5f4bb5ae49a8bd94ae6cbc.png
[sql]: https://bbs.csdn.net/topics/392047861
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTY5MTIyNw_size_16_color_FFFFFF_t_70_pic_center 26]: /images/20221122/32d7abb08f4a418c90fad85bedb9b645.png