数据库

第7讲：SQL约束和触发器

• 课堂讲稿：第7章的部分
• 书：第7章

数据库设计（续）

• 之前我们已经看到E-R图、功能依赖性和正常形式。
  • 这是关于如何组织数据库中的信息。
• 还有其他制约因素没有包括在高级别设计步骤。
  • 简单的事情，比如对属性值的限制：“idnr由十位数字组成”
  • 更复杂的限制。例如，涉及多张表格：“学生只有通过所有考试才能注册课程前提条件。"
  • 变更要求：“当学生从课程中注销时应该移动等待名单…”

数据库系统可靠性

• 今天：约束和触发器
  • 如何表达数据上的各种约束并确保数据库操作不会违反这些规则。
• 后期：交易和授权
  • 确保并发数据库访问不会损坏数据或者返回不一致的结果。为不同的用户提供不同的访问权限。

活动元素层次结构

从简单到强大

• 立柱类型
• 内联约束
• 约束条件
• 断言（未得到广泛支持）
• 触发器
• 应用程序代码
  • 最强大的方法也是造成最大伤害的方法如果你搞错了…

现场演示

文件夹

• 表格.sql
• 插入.sql
• 触发器.sql

活动元素层次结构

使用适当的列类型

• 这种工作方式…

  • 创建 表国家(名称TEXT主要 钥匙,人口TEXT,大陆文本,货币TEXT)；
    

• ……但这样更好：

  • 创建 表国家(名称TEXT主要 钥匙,人口INT,大陆CHAR(2),货币CHAR(三)
    )；
    

活动元素层次结构

使用内联约束进一步限制属性值

• 它们出现在每列定义的末尾。

• 创建 表换乘(发件人文本不是 无效的,接收方文本不是 无效的,金额INT检查 (数量>0),
  )；
  

• 创建 表已修课程(学生文本,课程文本,CHAR级(1) 检查 (等级英寸 (“U”,'3','4','5'))
  )；
  

• 主键、外键和NOT NULL是内联的示例我们以前见过的约束。

活动元素层次结构

涉及多个属性时使用约束

• 它们出现在所有列定义之后。

• 创建 表换乘(发件人文本,接收方文本,金额INT检查 (数量>0),
    检查 (接受者<>发件人)
  )；
  

• 禁忌可以命名为：

• 创建 表换乘(发件人文本,接收方文本,金额INT检查 (数量>0),
    约束不属于自己检查 (接受者<>发件人)
  )；
  

• 还与复合主键/外键一起使用。

模式匹配

检查字符串是否遵循给定格式

• 喜欢

  • 检查 (去甲柔红霉素喜欢 '%-%')
    

  • 检查 (去甲柔红霉素喜欢 '______-____')
    

  • “_”表示任何字符
  • “%”表示任意字符序列
• 类似（使用正则表达式）

  • 检查 (去甲柔红霉素类似 收件人 '[0-9]{10}-[0-9]{4}')
    

参考约束

• 创建 表转移(发件人文本参考文献账户(持有人)收件人文本参考文献账户(持有人)金额INT检查 (数量>0),
    …
  )；
  

• 但当我们想更改帐户持有人的姓名时会发生什么？

级联更改

• 创建 表转移(发件人文本参考文献账户(持有人) 打开 更新 级联,收件人文本参考文献账户(持有人) 打开 更新 级联,金额INT检查 (数量>0),
    …
  )；
  

• 通过使用关于更新级联,Accounts中主键的更改将传播到引用在转让中。
• 也可能：
  • ON UPDATE SET空
  • ON DELETE集合为空
  • 关于删除级联

断言

未得到广泛支持：-(

• 创建 断言最小余额AS公司
    检查 ( (选择 SUM（总和）(平衡) 发件人账户) >= 100000) )；
  

• PostgreSQL中不支持
• 次佳之选：触发器

触发器

• 数据库操作可以触发对函数的调用。
• 我们需要
  • 定义功能
  • 指定哪些操作触发对哪些函数的调用。

功能定义

• 创建 功能名称(参数) 退货类型AS公司 $$
  。。。
  $$ 语言语言；
  

• 可能的语言
  • SQL语言：纯SQL语句
  • plpgsql语言:PL/pgSQL–SQL过程语言。
  • …
• 你也可以写创建或替换功能…
• 函数可以用于触发器和其他用途。
• $$…文本$$是字符串文字，类似于'…文本…'。

表上的触发器

• 创建 触发器名称之后|之前 插入/更新/删除 打开桌子适用于 每个 世界其他地区|声明
    执行 功能函数_名称()；
  

• 您还可以组合事件

  • … 之后 更新 或 删除 打开桌子…
    

视图上的触发器

• 创建 触发器名称INSTEAD公司 第页，共页 插入|更新|删除 打开看法适用于 每个 世界其他地区|声明
    执行 功能函数_名称()；
  

• 通常，您可以在查询中使用视图，但不能对其进行修改。
• 使用而不是触发器，您可以更改基础响应的表插入,更新或删除在视图上。
• 这为我们提供了一种在应用程序和数据库表！

定义触发器中使用的函数

• 返回类型应为TRIGGER
• 每个函数都需要以RETURN语句结尾。
  • 在触发器中，您可以经常使用RETURN NULL…
• 当您希望触发器阻止对数据库的更改时：

  • RAISE（升起） 例外情况 '一些错误消息'；
    
    RAISE（升起） 例外情况 “总数只有%，太低了”,全部的；
    

• 如果要输出一些信息性消息（例如用于调试）

  • RAISE（升起） 通知 '一些信息性消息'；
    

• 请参见有关异常和消息的更多信息PostgreSQL参考手册。

新的和旧的

在为每行执行的触发器函数中

• 新增功能。属性_名称指属性的新值。
  • 可以在由INSERT或UPDATE触发的函数中使用。
  • 在这些函数中使用RETURN NEW。
• 旧款。属性_名称指属性的旧值。
  • 可以在由DELETE或UPDATE触发的函数中使用。

PL/pgSQL中函数中的局部变量

• 声明总INT；
    …
  开始全部的:= (选择 SUM（总和）(平衡) 发件人账户)；
    …
  完
  

PL/pgSQL中的IF语句

• 国际单项体育联合会条件那么
    …
  埃尔塞夫条件那么
    …
  其他
    …
  完 国际单项体育联合会；
  

原子事务

全有或全无语义

• SQL语句是原子的！
  • 即使它们影响了几行。
  • 约束可能会阻止INSERT或UPDATE完成。
• 这包括触发功能
  • 如果触发函数失败，则数据库中没有任何更改。
• SQL语句可以分组为较大的事务（在后面的讲座中介绍）。

再举一个例子

• 使用函数和触发器实现简单的等待列表。
• 触发器中未使用的函数示例。
• 文件：等待.sql。

进一步阅读

• Jonas Duregárd的幻灯片。
• 根据PostgreSQL参考手册：
  • 创建触发器。
  • 创建功能。
  • PL/pgSQL–SQL过程语言。

下一次

20/2星期四：替代数据模型，JSON。