ORACLE_OCP之约束(CONSTRAINT)

ORACLE_OCP之约束(CONSTRAINT)

  • 带着问题开始:
    • 什么是约束?
    • 约束应该如何维护?

一、什么是约束?

  • 约束就是在表上强制执行的规则
  • 约束可以防止在存在的相关性时产生误删除或者误操作
  • ORACLE包括以下几种约束:
    • 1 NOT NULL 非空约束 表中某列如果被定义为非空约束后 那么这一列是不允许有空值的。
    • 2 UNIQUE 唯一约束 表中某列如果被定义为唯一约束后 那么这一列中的额数据是不允许出现重复的
    • 3 PRIMARY KEY 主键约束 主键一般是一个表的具有标识性的列 并且主键列非空且唯一
    • 4 FOREIGN KEY 外键约束 在列和被引用的表的列之间建立一个外键关系[某种表表隐含连接关系]
    • 5 CHECK 检查约束 值当条件满足时 我们才能向该列中插入数据 或者做更新处理

二、约束的相关规则:

  • 1 可以给约束命名 但是尽量让他具有标识性 见名知意

  • 2 可以在一下任意时刻创建约束

    • A 当创建表的时候可以创建约束
    • B 当表创建好后 我们还可以进行相关约束的补充定义
  • 3 可以在列或者表级别上定义约束

  • 4 可以在数据字典表中查看相关约束的定义

  • 定义约束的小例子

    • 列级别约束 引用一个列 并在拥有的列的范围内定义。可以定义任何类型的约束条件

      CREATE TABLE TEST_1 (
      ID NUMBER PRIMARY KEY,
      NAME VARCHAR2(66)
      );

    • 表级别约束 引用一个或者多个列 并在该表的列定义中分别进行了定义 可以定义任何约束 但是NOT NULL除外

      CREATE TABLE TEST_2(
      ID NUMBER,
      NAME VARCHAR2(66),
      CONSTRAINT TEST_2_ID_PK PRIMARY KEY (ID));

  • 非空约束定义:

    • 非空约束必须要有值被真正的放进去,不能为空。

    CREATE TABLE TEST2(
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(66) NOT NULL
    );
    CREATE TABLE TEST2(
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(66) CONSTRAINT NAME_NN NOT NULL
    );

  • 唯一键约束:

    CREATE TABLE TEST3(
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(66) UNIQUE
    );
    CREATE TABLE TEST3_1(
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(66) ,
    CONSTRAINT NAME_uk unique(name)
    );

  • 主键约束

    • 主键约束 :PRIMARY KEY就代表一个表的主键【唯一且非空】

    CREATE TABLE TEST_1 (
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(66)
    );
    CREATE TABLE TEST_2(
    ID NUMBER,
    NAME VARCHAR2(66),
    CONSTRAINT TEST_2_ID_PK PRIMARY KEY (ID));

  • 主键约束 :PRIMARY KEY就代表一个表的主键【唯一且非空】

  • 外键约束

    • 外键将制指定一个列或者列组合作为外键,并建立与同一个表或不同表中的主键或唯一关键字之间的关系。

    • 被引用的表称之为附表,而引用的表或者被定义外键的表称之为子表。

    • 例子:

      create table dept(
      deptno number primary key,
      deptname varchar2(66)
      );
      create table emp(
      empno number primary key,
      ename varchar2(66),
      age number,
      dept number,
      constraint emps_dept_fk FOREIGN key(dept) REFERENCES dept(deptno)
      );

      • constraint emps_dept_fk --定义外键
      • FOREIGN key(dept) --定义该表中的哪个列是外键列
      • REFERENCES dept(deptno) --定义父表和父表中的列
    • 注意!我们在创建外键之前必须创建好父表

    • insert into dept values (1,‘sal’);
      insert into dept values (2,‘devp’);
      insert into emp values (1,‘zhang asn’,25,1);
      insert into emp values (2,‘li si’,26,1);
      insert into emp values (3,‘wangwu’,27,2);
      insert into emp values (4,‘chen liu’,28,null);–外键是可以为空的
      insert into emp values (5,‘chen liu’,28,null);–外键是可以出现重复值的
      delete from dept where deptno = 1; --当有外键子记录存在时不允许删除
      insert into dept values (3,‘opr’); --当不存在子记录的时候是可以删除的
      delete from dept where deptno = 3;–外键上的值可以随便删除

    • on delete cascade级联删除语句:当父表中数据被删除以后,子表中包含该父表的数据的行数也被级联删除了。【删除一整行】

    create table emp1(
    empno number primary key,
    ename varchar2(66),
    age number,
    dept number,
    constraint emps1_dept_fk FOREIGN key(dept) REFERENCES dept(deptno) on delete cascade
    );

    • on delete set null级联删除语句:当删除父表中的数据时,子表中包含父表该数据的行数据被替换成null值。【单个数据,或者就是父表中数据的那个对应值被改成null】

    create table emp2(
    empno number primary key,
    ename varchar2(66),
    age number,
    dept number,
    constraint emps2_dept_fk FOREIGN key(dept) REFERENCES dept(deptno) on delete set null
    );

    • 当该列(字表中的foreign key)约束为not null时,删除父表的对应项,就会报错(因为字表中这个项应该置为空,但是在建表的时候我们约束了不能为空,所以冲突,报错)

    //建表,设置dept,这个外键为非空。
    create table emp3(
    empno number primary key,
    ename varchar2(66),
    age number,
    dept number not null,
    constraint emps3_dept_fk FOREIGN key(dept) REFERENCES dept(deptno) on delete set null
    );
    //删除报错。
    delete from dept where deptno = 2; --报错 ORA-01407: 无法更新 (“HR”.“EMP3”.“DEPT”) 为 NULL
    因为我们在建立emp3的时候 就已经规定了dept这个列/字段是不能为空的

  • CHECK约束

    • 1 用于定义每行都必须满足的条件
    • 2 以下表达式不允许的
      • A 对CURRVAL NEXTBAL LEVEL 和ROWNUM伪劣引用是不允许的
      • B 对SYSDATE UID USER 和USERENV函数调用也是不允许的
      • C 涉及其他行中的其他值的查询
    • 例子

      CREATE TABLE TEST_C
      (ID NUMBER CONSTRAINT TEST_C_ID_MIN CHECK (ID > 10)
      );
      INSERT INTO TEST_C VALUES (11); --允许插入 因为满足了ID > 10
      INSERT INTO TEST_C VALUES (10); --不允许插入 因为不满足ID > 10

三、添加、删除、修改约束:

  • 使用ALTER TABLE语句进行以下任务
    添加、删除、修改相关约束 。
    但是对约束的操作并不破坏表结构

  • 添加约束

    • 添加外键

    ALTER TABLE EMP4 ADD constraint emps4_dept_fk FOREIGN key(dept) REFERENCES dept(deptno);

    • 添加主键

    ALTER TABLE EMP4 ADD constraint emps4_empno primary key(empno);

  • 删除约束

    ALTER TABLE EMP4 DROP CONSTARINT emps4_empno;

    • 如果我要删除dept表上的主键 并删除emp4表上的关联的外键约束。

    ALTER TABLE DEPT DROP PRIMARY KEY; --不对 因为会报错 告诉你 你的主键被其他表当外键
    ALTER TABLE DEPT DROP PRIMARY KEY CASCADE; 可以在删除主键的时候级联删除掉外键

    • 测试:

    create table dept1(
    deptno number primary key,
    deptname varchar2(66)
    );
    ALTER TABLE EMP4 ADD constraint emps4_dept_fk FOREIGN key(dept) REFERENCES dept1(deptno);
    DROP TABLE DEPT1 PURGE; --表删除失败 因为你并未解除和其他表的主外键关系 所以删除失败

  • 禁用约束/启用约束

    ALTER TABLE EMP4 DISABLE CONSTRAINT emps4_dept_fk;–禁用外键约束
    ALTER TABLE EMP4 ENABLE CONSTRAINT emps4_dept_fk;–启用外键约束
    ALTER TABLE EMP4 DISABLE CONSTRAINT dept.deptno;

    • 测试实例

    create table dept2(
    deptno number ,
    deptname varchar2(66)
    constraint dept2_deptno_pk primary key(deptno)
    );
    create table emp5(
    empno number primary key,
    ename varchar2(66),
    age number,
    dept number not null,
    constraint emps5_dept_fk FOREIGN key(dept) REFERENCES dept2(deptno)
    );
    ALTER TABLE DEPT2 DISABLE CONSTRAINT dept2_deptno_pk CASCADE;
    ALTER TABLE DEPT2 DISABLE CONSTRAINT dept2_deptno_pk CASCADE;–有主外键关系时听主键一定要设置级联条件
    ALTER TABLE DEPT2 ENABLE CONSTRAINT dept2_deptno_pk;–主键恢复

  • [删除]相关的级联约束–删除列的方式,删除列上相关约束和其像关联的其他约束(外键)

    ALTER TABLE DEPT2 DROP (DEPTNO) CASCADE CONSTRAINTS;

    • 如果没有相关的约束 我们可以直接用ALTER TABLE T_NAME DROP(COL_NAME) 删除列
      但是有约束时,并且像是那种主外键级联约束的话 就需要使用CASCADE CONSTRAINTS;
      因为我这个列 极有可能是本表的主键,并且还有可能是其他表的外键 所以这里有格外的注意。
  • 查看相关的约束

    SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE ,SEARCH_CONDITION
    FROM user_constraints
    where table_name=‘EMPLOYEES’;
    在这里插入图片描述
    SELECT CONSTRAINT_NAME,COLUMN_NAME
    FROM user_cons_columns
    WHERE TABLE_NAME=‘EMPLOYEES’;
    在这里插入图片描述

  • 1
    点赞
  • 0
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

打赏
文章很值,打赏犒劳作者一下
相关推荐
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页

打赏

XiaoHG_CSDN

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者