011. Oracle-约束

我 的 个 人 主 页：👉👉 失心疯的个人主页 👈👈
入 门 教 程 推 荐 ：👉👉 Python零基础入门教程合集 👈👈
虚 拟 环 境 搭 建 ：👉👉 Python项目虚拟环境(超详细讲解) 👈👈
PyQt5 系 列 教 程：👉👉 Python GUI(PyQt5)文章合集 👈👈
Oracle数据库教程：👉👉 Oracle数据库文章合集 👈👈
优 质 资 源 下 载 ：👉👉 资源下载合集 👈👈
优 质 教 程 推 荐：👉👉 Python爬虫从入门到入狱系列 合集👈👈

约束

• 学习内容
  1. 维护数据的完整性
  2. 管理索引
  3. 管理权限和角色
• 学习目标
  1. 掌握维护oracle数据完整性的技巧
  2. 理解索引概念，会建立索引
  3. 管理oracle的权限和角色
• 维护数据的完整性
  • 数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则
  • 在oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现
  • 在这三种方法中，约束易于维护，并且具有最好的性能。所以约束作为维护数据完整性的首选

---

约束

• 约束用于确保数据库数据满足特定的商业规则。
• 在oracle中约束包括：not null(非空)、unique(唯一)、primary key(主键)、foreign key(外键)和check(检查)五种
• 在定义约束的时候，可以为约束定义名称，方便后期管理（删除、停用、激活）
• not null(非空)
  • 如果在列上定义了not null(非空约束)，那么当插入数据时，必须为列提供数据
• unique(唯一)
  • 当在列上定义了unique(唯一约束)，该列值是不能重复的，但是可以为null
• primary key(主键)
  • 用于唯一的标示表行的数据，当定义primary key(主键约束)后，该列不但不能重复，而且不能为null
  • 说明：一张表最多只能有一个主键（可以有联合主键，多列组合起来同时作为主键），但是可以有多个unique约束
• foreign key(外键)
  • 用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null
• check(检查)
  • 用于强制行数据必须满足的条件。
  • 假定在sal列上定义了check约束，并要求sal列值在1000~2000之间。如果传入的值不在1000~2000之间，就会提示出错

案例

• 商店售货系统表设计案例
  • 现有一个商店的数据库，记录客户及其购物情况。由下面三个表组成
  1. 商品表goods(商品号goodsid,商品名goodsname,单价unitprice,商品类别category,供应商provider)
  2. 客户customer(客户号customerid,姓名name,住址address,电邮email,性别sex,身份证cardid)
  3. 购买purchase(客户号customerid,商品号goodsid,购买数量nums)
• 建表
• 要求：
  1. 每个表的主外键
  2. 客户的姓名不能为空值
  3. 单价必须大于0，购买数量必须在1到30之间
  4. 电邮不能重复
  5. 客户的性别必须是男或女，默认是男
• 代码

  >> 创建商品表
   create table goods(
      goodsid char(8) primary key,
      goodsname varchar2(30),
      unitprice number(10,2) check(unitprice > 0),
      category varchar2(10),
      provider varchar2(50)
      );
  
  >> 创建客户表
   create table customer(
      customerid char(8) constraint cust_id_pk primary key,
      name varchar2(10) constraint cust_name_not not null,
      address varchar2(50),
      email varchar2(30) constraint cust_ema_uk unique,
      sex char(2) default '男' constraint cust_sex_ck check (sex in ('男','女')),
      cardid varchar2(18)
      );
  
  >> 创建购买表
  create table purchase(
      customerid char(8) constraint pur_custid_fk references customer(customerid),
      --设置外键。references指向某张表的某个字段
      goodsid char(8) constraint pur_goodid_fk references goods(goodsid),
      nums number(2) constraint pur_num_ck check (nums between 1 and 30)
      );
  

• 补充约束
  • 如果在建表时忘记建立必要约束，则可以在建表后使用alter table命令为表增加约束。
  • 注意：增加not null约束时，需要用modify选项，而增加其他四种约束使用add选项
• 要求
  1. 增加商品名也不能为空
  2. 增加身份证也不能重复
  3. 增加客户的住址只能是’海淀’、‘朝阳’、‘东城’、‘西城’、‘通州’、‘崇文’
• 代码

  >> 增加商品名不为空
      alter table goods modify goodsname not null;
  
  >> 增加身份证不能重复
      alter table customer add constraint cust_car_uk unique(cardid)
      -- alter table 表名 add constraint 约束名 unique(约束字段名)
      -- 修改 表 表名 增加 约束 自定义约束名 约束类型(字典名)
  
  >> 增加客户住址限定区域
      alter table customer add constraint cust_add_ck check(address in ('海淀','朝阳','东城','西城','通州','崇文'))
  

• 删除约束
  • 当不需要某个约束时，可以删除
  • 删除表的主键：由于一个表的主键是唯一的，所以无须指定主键名
  • 删除表的其他约束：一个表的约束可能有多个，所以必须指定约束的名称

    alter table customer drop primary key; --删除主键约束
    
    alter table customer drop constraint cust_car_uk; --删除其他约束
    alter table 表名 drop constraint 自定义约束名称;
    

  • 注意：在删除约束时，可能由错误。比如：

    alter table 表名 drop constraint 自定义约束名;
    

  • 这是因为如果在两张表存在主从关系，那么在删除主表的主键时，必须带上cascade选项

    alter table 表名 drop constraint 自定义约束名 cascade;
    

• 停用约束
  • 使某一个约束失效:此约束还存在于表中，只是不起作用

    alter table customer disable constraint cust_add_ck;
    

• 激活约束
  • 使某一个约束激活：激活以后，此约束具有约束力

    alter table customer enable constraint cust_add_ck
    

• 显示约束信息
  • 显示约束信息（通过查询数据字典视图user_constraints可以显示当前用户所有的约束信息）

    select constraint_name,constraint_type,status,validated from user_constraints where table_name='表名'
    

  • 显示约束列（通过查询数据字典视图user_cons_columns可以显示约束所对应的表列信息）

    select column_name,position from user_cons_columns where constraint_name='约束名'
    

  • 也可以之间用pl/sql developer查看约束

---

列级定义和表级定义

• 列级定义：在定义列的同时定义约束
• 表级定义：先定义了所有列，再定义约束

约束名	说明	备注
not null	不为空约束	只能定义在列级
unique	唯一约束	可以定义在表级或列级
primary key	主键	可以定义在表级或列级
foreign key	外键	可以定义在表级或列级(配合references)
check	定义每一行必须满足的条件	可以定义在表级或列级

• 示例

>>> 列级定义约束
create table myinfo(
    myid char(4) constraint info_id_pk primary key,
    myname varchar2(30) constraint info_name_null not null,
    mysex char(2) constraint info_sex_ck check(mysex in ('男','女')),
    myident char(18) constraint info_ident_uk unique
);


>>> 表级定义约束
create table mygrade(
    myid char(4), total number(3),
    constraint grade_id_fk foreign key(myid) references myinfo(myid), 
    constraint grade_tal_ck check(total>0 and total<500)
);