精通Oracle核心技术和项目实战之表的约束

第6章表的约束 约束是每个数据库必不可少的一部分 约束的根本目的在于保持数据的完整性 数据完整性是指数据的精确性和可靠性 即数据库中的数据都符合某种预定义规则 当用户输入的数据不符合这些规则时 将无法实现对数据库的更改 本章将主要介绍主键 外键 唯一性 检查 默认值几种约束 并讲解如何利用这些约束保持数据完整性 6 1约束简介 Oracle的主要约束即指列的约束 主要包括主键约束 外键约束 唯一性约束 检查约束 默认值约束 非空约束 表中的约束条件可以保证表中数据的完整性 6 1约束简介 在Oracle11g中 提供了多种机制来实现数据库的完整性 主要有三种 域完整性 是对数据表中字段属性的约束 包括字段的值域 字段的类型及字段的有效规则等约束 是由确定关系结构时所定义的字段的属性决定的 域完整性主要包括notnull约束 唯一性约束 检查约束 实体完整性 即指关系中的主属性值不能为null且不能有相同值 实体完整性是对关系中的记录唯一性 也就是主键的约束 参照完整性 即指关系中的外键必须是另一个关系的主键有效值 或是参照完整性是对关系数据库中建立关联关系的数据表间数据参照引用的约束 也就是对外键的约束 6 2主键约束 主键约束是数据库中最常见的约束 可以保证数据完整性 通过将主键纳入查询条件 可以达到查询结果最多返回一条记录的目的 本节将主要介绍主键的添加 删除 使用 6 2 1主键简介 主键被创建在一个或多个列上 通过这些列的值或者值的组合 唯一地标识一条记录 通过为表customersnew添加主键 customer id 即可保证数据表的数据不会出现重复 这也是我们常说的实体完整性 实体完整性规则是针对现实世界的一个实体集 而现实世界中的实体是可区分的 实体完整性的目的是利用关系模式中的主键或主属性来区分现实世界中的实体集中的实体 所以不能取空值 实体完整性约束是通过定义primarykey约束来实现的 定义为primarykey约束的列被称为 主鍵列 6 2 2利用命令添加主键约束 主键约束一般在建表时创建 如果建表时没有创建主键约束 我们可以直接添加主键约束 Oracle中提供关键字primarykey来实现主键约束的添加 添加主键约束语法如图所示 其中约束名是可省的 如果没有为约束指定名称 那么名称将自动产生 altertable命令用于修改表的属性 primarykey 列名1 列名2 则指定主键被建立在哪些列之上 各列名之间使用逗号进行分隔 注意 主键约束在每一个数据表中只有一个 但是主键约束可以由多个列组成 6 2 2利用命令添加主键约束 示例6 1 为customersnew表添加主键约束 以customer id这一列添加主键约束为例说明 主键约束名为customersnew pk 示例6 2 通过数据字典user constraints查看成功创建的主键约束的详细信息 示例6 3 利用主键约束的名称 customersnew pk 来获得主键被建立在哪些列之上 6 2 3主键使用场景 主键是数据表中最常用的约束 其应用场景一般有如下几种 1 对于完整性要求比较高的数据表应该建立主键主键可以保证数据在主键列上的唯一性 对于特定的业务逻辑 可以指定相应的主键规则 避免出现错误数据 例如 人员信息数据表 可以考虑以人员的身份证号作为主键 避免出现重复 2 对于经常按照某列进行查询的数据表 应该考虑建立主键 3 考虑对外键是否有利 6 2 3主键使用场景 当用户对基表插入一条记录或者对主键列进行更新操作的时候 关系数据库管理系统将自动进行检査 包括 检査主键值是否唯一 如果不唯一则拒绝插入或者修改 检查主键的各个属性是否为空 如果有一个为空 则拒绝插入或者修改 6 2 3主键使用场景 当在一个表上创建主键之后 向表中添加数据就有了约束 此时 输入数据时表中所有数据在主键列上的值 或值的组合 不能重复 并且主键列上的值不能为空 6 2 3主键使用场景 示例6 4 尝试向表customersnew中插入已存在的customer id的记录 Oracle将抛出错误提示 并禁止用户的插入操作 示例6 5 如果表中创建主键约束后 除了不能向表customersnew中插入customer id冲突的数据之外 也不能插入customer id为空的数据 6 2 4修改主键约束 主键约束创建之后 可以对其进行属性修改 修改主键约束主要包括两方面的内容 禁用 启用主键和重命名主键 6 2 4修改主键约束 1 禁用 启用主键在Oracle中 对于任何已经创建好的约束 可以使用ENABLE或者是DISABLE主键来启用约束或者是禁用约束 禁用的约束好像并没有创建约束一样 并没有达到创建约束的目的 但是通过ENABLE子句 可以随时启用约束 禁用 启用数据表主键的语法如图所示 6 2 4修改主键约束 示例6 6 表customersnew的主键的当前状态为可用 那么不允许插入customer id与已有记录冲突的数据 现禁用该主键 并再次尝试插入数据 示例6 7 上一示例中 我们将主键禁用 并插入了customer id与已有记录冲突的数据 当启用主键约束时 Oracle仍然会检查数据表中现有数据是否违反了主键约束 如果违反 那么主键的启用也将失败 6 2 4修改主键约束 2 重命名主键如果需要修改已有主键的名称 则需要对约束进行重命名 重命名主键的语法如图所示 示例6 8 将数据表customersnew的主键约束customersnew pk重命名为cus pk 6 2 5删除主键约束 如果希望去掉表上的某个约束 可以将其禁用 使其无效 也可以将其删除 如果在表上已经建立了一个约束 现在希望把它改为另一类型的约束 或者希望把它施加在另一个列上 此时约束是不能被修改的 只能先将这个约束删除 然后重新创建 6 2 5删除主键约束 1 用命令方式删除主键删除约束是通过执行alter命令的drop子句来完成的 删除约束的alter命令的语法格式如图所示 示例6 9 删除customersnew表的主键约束 6 2 5删除主键约束 2使用SQLDeveloper删除主键约束除了命令方式删除键之外 还可以用SQLDeveloper删除主键约束 重新为表customersnew表的customer id这一列添加主键约束 6 2 6主键与索引 索引可以优化对表的查询操作 如果用户在数据表的列上创建了主键 而在检索数据时又使用了该索引列 Oracle可以很快的捕获符合条件的记录 数据库中的索引 使数据库无需对这个那个表进行扫描 就可以再其中找到所需的数据 有关索引的详细信息 将在以后的章节讲解 6 2 6主键与索引 示例6 10 在Oracle中 创建了主键之后 都会存在一个建立在主键列上的索引 例如 表customersnew的主键CUSTOMERSNEW PK建立在列customer id上 我们可以通过视图user indexes获得表customersnew上索引的详细信息 示例6 11 将数据表customersnew的主键重命名 查看索引信息 随后将主键删除 查看索引信息 6 3外键约束 外键约束可以保证使用外键约束的数据库列与所引用的主键约束的数据列一致 外键约束在一个数据表中可以有多个 本节将主要介绍外键的创建 删除 使用 6 3 1外键简介 foreignkey约束即外键约束 通过使用外键 保证表与表之间的参照完整性 定义了foreignkey约束的列称为 外键列 被foreignkey约束引用的列称为 引用列 包含外键的表称为子表 也称为引用表 包含引用列的表称为父表 也称为被引用表 通过使用公共列在表之间建立一种父子关系 在表上定义的外键可以指向主键或者其他表的唯一键 6 3 2添加外键约束 创建外键 首先在主表中创建主键 因为当使用外键寻找主表中记录时 必须要找到唯一一条 所以必须在主表中使用主键来标识记录的唯一性 在Oracle中提供关键字foreignkey来实现外键约束的添加 6 3 2添加外键约束 示例6 12 为customersnew表添加外键约束 我们以customersnew表account mgr id这一列添加外键约束为例说明 示例6 13 上一示例中 创建了外键cus account mgr fk 通过视图user constraints获取外键的详细信息 示例6 14 通过视图user cons columns 可以获取外键cus account mgr fk的列信息 6 3 3验证外键约束 外键约束创建成功之后 当添加或者修改外键列为不合理数据时 外键约束将禁止该动作的执行 示例6 15 向表customersnew中插入新的数据 account mgr id列的值为001 示例6 16 查询表employees中employee id的数据 再次插入顾客信息 示例6 17 当向子表插入数据时 会受到外键约束 数据修改操作同样会受到外键约束的限制 示例6 18 创建外键约束后 如果要删除父表中的数据 对应的子表发生的变化 6 3 4修改外键约束 删除外键约束与删除主键约束语法一致 在Oracle中提供了SQL语句dropconstraint来实现外键约束的删除 语法如图所示 示例6 19 删除表customersnew中的外键cus account mgr fk 示例6 20 删除表customersnew的外键之后 便可以为其创建级联删除外键 创建外键约束后 如果要父表中的数据删除后 对应的子表也发生变化 6 3 5使用SQLDeveloper添加 删除外键约束 现在我们用SQLDeveloper为customersnew表添加 删除外键约束 在一个表上创建外键之前 被引用表必须已经存在 并且必须为该表的引用列定义唯一性约束或者主键约束约束 6 3 5使用SQLDeveloper添加 删除外键约束 在定义外键约束约束时 还可以通过关键字on指定引用行为的类型 当尝试删除被引用表中的一条记录时 通过引用行为可以确定如何处理外键表中的外键列 引用类型包含如下几种 cascade关键字 表示当父表中的被引用列的数据被删除时 子表中对应的外键数据也将被删除 setnull关键字 表示当父表中的被引用列的数据被删除时 子表中对应的外键数据将被设置为null restrict关键字 表示拒绝对父表的删除或更新操作 6 3 6外键使用场景 外键使用场景有如下几种 1 严格遵守父子关系的数据表应该使用外键 2 将应用程序中的父子关系转移到外键约束 3 不要过分使用外键 6 4唯一性约束 UNIQUE约束 唯一性约束和之前的主键约束很相似 不同的是就是唯一约束在一个表中可以有可以有多个 而主键约束在一个表中只能有一个 本节将详细讲述唯一性约束的添加 删除 使用 6 4 1唯一性约束简介 正如在6 1节中所描述的那样 主键列上的值都是唯一的 主键是记录唯一性的保证 但是 一个表只能有一个主键 很多时候 对于其他列同样要求列值唯一 例如 在customersnew表中 列customer id作为主键可以保证用户的唯一性 但是同时又要求其phone number电话号码唯一 防止多个用户同时使用同一联系方式 所以 可以这样理解 主键设计为标识唯一的一条记录 而唯一性约束则作为主键的有力补充 6 4 2添加唯一性约束 在创建数据表后 我们会发现有些列也需要确定唯一性 结合生活中的实例 如电话号 邮箱等 这时候我们可以引进唯一性约束来实现表的完整性 如同我们前面讲的 语法结构类似 如图所示 6 4 2添加唯一性约束 示例6 21 为customersnew表中 列phone number上添加唯一性约束为例说明 示例6 22 查看customersnew表中的唯一约束信息 6 4 3验证唯一性约束 customersnew表中 customer id为982的顾客的phone number为 868236566 创建唯一约束之后 这条数据的phone number是唯一的 如果你再添加一条数据电话号重复的话 Oracle会提示出错 示例6 23 唯一性约束列允许空值存在 我们可以将列phone number的数据设置为空 再次进行验证 6 4 4修改唯一性约束 唯一性约束创建之后 可以对其进行删除 重命名 禁用 启用等操作 1 重命名唯一性约束唯一约束创建之后 可以对其进行重命名 其语法形式如图所示 6 4 4修改唯一性约束 示例6 24 例如 将表customersnew上创建的唯一性约束uk phone重命名为uk ph 那么 可以利用rename选项进行重命名 6 4 4修改唯一性约束 2 禁用 启用唯一性约束约束创建成功之后 除了可以重命名外 还可以对其进行禁用 启用操作 禁用之后 Oracle不再利用该约束进行校验 但是 当启用时 将对表中所有数据进行校验 禁用 启用唯一性约束的语法如图所示 示例6 25 禁用表customersnew上的唯一性约束uk ph 6 4 4修改唯一性约束 3 删除唯一性约束删除唯一性约束与删除其它约束语法一致 在Oracle中提供了SQL语句dropconstraint来实现外键约束的删除 语法如图所示 示例6 26 为customersnew表删除唯一性约束为例说明 6 4 5使用SQLDeveloper添加 删除唯一性约束 用SQLDeveloper为customersnew表添加 删除唯一性约束与前面的其它约束基本一致 6 4 6唯一性约束使用场景 唯一性约束可以建立在列或者列的组合上 可以作为主键约束的补充 常见的情形为 主键列为id 而id只是一个自增的整数序列 与实际的业务逻辑无关 在业务逻辑上保证记录的唯一性往往使用唯一性约束来实现 6 5检查约束 在前面介绍的约束 主键 外键 唯一性约束 实际在定义多个列值之间的关系 例如 主键和唯一性都约束表中的两个列值或列值组合它们不能相同 而外键则约束了两个表之间的数据保持父子关系 本节介绍的检查约束则是针对列值本身进行限制 6 5 1检查约束简介 检查约束是一个关系表达式 它规定了一个列必须满足的条件 例如员工的性别只能是 男 或 女 员工的工资必须在1000 8000等 当向表中插入一行 或者修改某一行时 都要检查指定列的值是否满足这个条件 如果满足 这个操作才能成功执行 对于数据表customersnew来说 顾客的赊销限额credit limit是大于0的 说明如图所示 6 5 2添加检查约束 在建表后 我们会发现有些列也需要确定唯一性 这时候我们可以引进检查约束约束来实现表的完整性 语法结构如图所示 6 5 2添加检查约束 示例6 27 为customersnew表添加检查约束 限定赊销限额credit limit是大于等于0的 示例6 28 利用数据字典user constraints与user cons columns 查询customersnew表中 查看检查约束的详细信息 6 5 3验证检查约束 在成功创建检查约束之后 可以向其中插入非法数据 以验证其作用 示例6 29 向数据表customersnew插入一条credit limit值小于0的一条数据 验证检查约束 示例6 30 当向表customersnew插入credit limit为 1000的数据时 违反了检查约束ch credit Oracle抛出错误提示 并禁止数据的插入 将非法数据 1000修改为1000 再次尝试插入操作 将可以通过检查约束的校验 并成功执行 6 5 3验证检查约束 示例6 31 检查约束可以对多个条件同时进行校验 使用逻辑运算符将这些条件进行联接 创建检查约束使得表customersnew中 customer id大于0 并且city不为空 6 5 4修改检查约束 检查约束创建之后 可以对检查约束进行重命名 删除和禁用 启用等操作 1 重命名检查约束检查约束的重命名与其他约束的重命名具有完全相同的语法 即利用rename选项 示例6 32 将检查约束ch id city重命名为ck id city 6 5 4修改检查约束 2 禁用 启用检查约束检查约束的禁用 启用操作也是使用disable enable选项 示例6 33 禁用检查约束ck id city 6 5 4修改检查约束 3 删除检查约束删除检查约束与删除其它约束语法一致 在Oracle中提供了SQL语句dropconstraint来实现检查约束的删除 语法如图所示 6 5 4修改检查约束 示例6 34 为customersnew表删除检查约束ck id city为例说明 示例6 35 当成功删除检查约束ck id city之后 数据字典将不再保留其信息 6 5 5使用SQLDeveloper添加 删除检查约束 用SQLDeveloper为customersnew表添加 删除检查约束与前面的其它约束基本一致 具体编辑好后 单击 确定 按钮即可 6 5 6检查约束使用场景 检查约束可以通过很灵活的约束条件来完成约束任务 但是不能过多使用检查约束 尤其是复杂的约束条件 因为针对每条记录的插入或更新操作 都需要进行检查约束的校验 并耗费大量资源 6 6非空约束 notnull约束 非空约束经常会在创建表时添加非空约束以确保字段必须要输入值 该约束和之前讲的约束不同 是直接在创建列时设置字段的非空约束 本节就简单介绍一非空约束的添加以及撤销 6 6 1为列添加非空约束 在建表后 我们会发现有些列需要确定非空 比如grade表 学号为必填等 在Oracle中 除notnull约束以外 其余的主键约束 外键约束 唯一性约束 检查约束四种约束都可以通过add子句添加 notnull约束约束比较特殊 只能通过alter命令的modify子句来添加 添加notnull约束的语法如图所示 6 6 1为列添加非空约束 示例6 36 对于表的列来说 默认情况下允许为空 例如 对于表customersnew 利用desc命令查看表结构及相关信息 示例6 37 为customersnew表中的cust first name添加notnull为例说明 示例6 38 在数据字典user tab columns中可以查看其非空约束的相关信息 示例6 39 非空约束要求列值不能为空 在表customersnew表中 列cust first name具有非空约束 向其中插入非法以验证非空约束的作用 6 6 2撤销非空约束 撤销非空约束也很简单 语法如图所示 示例6 40 将customersnew表中的cust first name设为null为例说明 示例6 41 撤销非空约束之后 再次向表中插入新的记录 此时列cust first name的值可以为空 6 7默认约束 与null值相似 在创建表