oracle9i学习笔记之九 约束

 1.约束类型
  NOT NULL----指定列不能包含空值
  UNIQUE-----指定列的值或者列的组合的值对于表中所有的行必须是唯一的
  PRIMARY KEY----表的每行的唯一性标识
  FOREIGN KEY----在列和表引用表的一个列之间建立并强制一个外键关系
  CHECK----指定一个必须为真的条件

2.约束原则
  1)命名一个约束，必须按照命名规则。如果不命名约束，Oracle服务器将用SYS_Cn格式产生一个名字，这里n是一个唯一的整数，所以约束名是唯一的。
  2)可以在创建表的同时，或者创建表之后创建一个约束
  3)在列或者表级定义一个约束
  4)在数据字典USER_CONSTRAINTS中查看约束
 
3.定义约束
  CREATE TABLE [schema.]table
               (column datatype [DEFAULT expr]
                [column_constraint],
                ...
                [table_constraint]);
  CREATE TABLE emp(
                   empno NUMBER(4),
                   ename VARCHAR2(10),
                   ...
                   deptno NUMBER(7,2) NOT NULL,
                   CONSTRAINT emp_empno_pk PRIMARY KEY (empno));

1)列约束级：只能引用一个列，属于列定义
  column [CONSTRAINT constraint_name] constraint_type,

2)表约束级：可引用一个或多个列，属于表定义，可以定义为除NOT NULL以外的约束
  column,...
          [CONSTRAINT constraint_name] constraint_type
          (column,...),

3)约束通常在创建表的同时被创建，在表创建后约束能被添加，且约束可被临时禁用
  约束可以在两个级别上定义：
 
   约束级别                           说明
      列           只涉及一个单个的列，对该列用规范定义；
                   能够定义完整性约束的任何类型
      表           涉及一个或多个列，表中的列被分别定义；
                   除了NOT NULL，能够定义任何约束

4.NOT NULL约束
  -确保某些列不允许空值：
    -NOT NULL约束：对该列来说没有行能够包含一个空值
    -缺少NOT NULL约束：对于该列来说任何行都能包含空值
  -NOT NULL约束确保列不包含控制。默认情况下，没有此约束，可包含空值

  例1：在列一级上定义约束
       CREATE TABLE emp(
              empno     NUMBER(4),
              ename     VARCHAR2(10) NOT NULL,
              job       VARCHAR2(9),
              mgr       NUMBER(4),
              hiredate  DATE,
              sal       NUMBER(7,2),
              comm      NUMBER(7,2),
              deptno    NUMBER(7,2) NOT NULL);

5.UNIQUE约束
  -UNIQUE完整性约束要求在表中指定的列或列组合中不能有两行相同的值。定义UNIQUE约束的列（或列组合）被称为唯一键（unique key）
  -除非我们在相应列上定义了NOT NULL约束，否则UNIQUE约束允许输入空值
  -UNIQUE可以在列级也可以在表级定义

  例1：定义在表一级
      CREATE TABLE deptment(
             deptno     NUMBER(2),
             dname      VARCHAR2(14),
             loc        VARCHAR2(13),
             CONSTRAINT dept_dname_uk UNIQUE(dname));

  例2：定义在列一级
       CREATE TABLE deptment(
              deptno  NUMBER(2),
              dname   VARCHAR2(14) CONSTRAINT dept_dname_uk UNIQUE,
              loc     VARCHAR2(13));

6.PRIMARY KEY约束
  -PRIMARY KEY约束为表创建一个主键
  -每个表只能创建一个主键
  -PRIMARY KEY约束是表中对行唯一标识的一个列或列组合，该约束强制列或列组合的唯一性
  -主键列不能包含空值
  -可定义在表一级或列一级
 
  例1：定义在表一级
       CREATE TABLE deptment(
              deptno     NUMBER(2),
              dname      VARCHAR2(14),
              loc        VARCHAR2(13),
              CONSTRAINT dept_dname_uk UNIQUE(dname),
              CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));

  例2：定义在列一级
       CREATE TABLE deptment(
              deptno     NUMBER(2) CONSTRAINT dept_deptno_pk PRIMARY KEY,
              dname      VARCHAR2(14),
              loc        VARCHAR2(13),
              CONSTRAINT dept_dname_uk UNIQUE(dname));

7.FOREIGN 约束 
  -可定义在表或列一级
  例：列一级约束：
      CREATE TABLE emp(
             empno    NUMBER(4),
             ename    VARCHAR2(10) NOT NULL,
             job      VARCHAR2(9),
             mgr      NUMBER(4),
             hiredate DATE,
             sal      NUBMER(7,2),
             comm     NUMBER(7,2),
             deptno   NUMBER(7,2) NOT NULL
               CONSTRAINT emp_deptno_fk REFERENCES dept(deptno));
  -FOREIGN KEY:在表或列约束级别
  -REFERENCES:标识父表和父表中列
  -ON DELETE CASCADE:当父表中的行被删除时，删除子表中相依赖的行
  -ON DELETE SET NULL:当父表的值被删除时，转换外键值为空
  -在无ON DELETE CASCADE或ON DELETE SET NULL选项时，若父表中的行在子表中引用，那么它不能被删除
 
  例：ON DELETE CASCADE选项
      CREATE TABLE test(
             empno      NUMBER(4),
             ename      VARCHAR2(10) NOT NULL,
             deptno     NUMBER(7,2) NOT NULL
             CONSTRAINT emp_deptno_fk
                        REFERENCES dept(deptno)
                        ON DELETE CASCADE);

8.CHECK约束
  -CHECK约束定义一个每行都必须满足的条件，该条件可以使用和查询条件一样的结构
   salary NUMBER(8,2) CONSTRAINT
   department_salary_min CHECK(salary>0),
   ...
  -不允许使用下面的表达式：
     -引用CURRVAL,NEXTVAL,LEVEL和ROWNUM伪列
     -调用SYSDATE,UID,USER和UDERENV函数
     -涉及其它行的值

9.改变约束
  用ALTER TABLE语句：
  -添加或删除玉树，但不修改它的结构
  -启用或禁用约束
  -用MODIFY子句添加一个NOT NULL约束

1)添加约束
  ALTER TABLE table
    ADD [CONSTRAINT constraint] type (column);
  其中：
       table----表的名字
       constraint----约束的名字
       type----约束的类型
       column----受约束影响的列的名字

  例1：添加一个约束到emp表，要求薪水大于零
       ALTER TABLE emp
         ADD CONSTRAINT emp_sal
         CHECK (sal>0);
 
  例2：添加一个FOREIGN KEY约束到emp表，指示经理必须已经是emp表中的职员
       ALTER TABLE emp
         ADD CONSTRAINT emp_mgr_fk
         FOREIGN KEY(mgr)
         REFERENCES emp (empno);

  可以用ALTER TABLE语句的MODIFY子句添加一个NOT NULL约束到一个已经存在的列
  例：
     ALTER TABLE emp
       MODIFY (salary CONSTRAINT emp_salary_nn NOT NULL)；
  注：只有在表示空的或者每个行的该列都有非空值的情况下，才可以定义NOT NULL

2)查看约束
  -数据字典user_constraints
   SELECT table_name,constraint_name,constarint_type,status
   FROM   user_constraints
   WHERE  table_name='EMP';  //表名必须大写

  -在constraint_type中，C代表CHECK，P代表PRIMARY KEY，R代表引用完整性，U代表UNIQUE键。NOT NULL 在数据字典中被作为CHECK约束存储
  
  -可用USER_CONS_COLUMNS数据字典视图查看与约束相关的列名。该视图对于由系统指定名字的约束特别有用

   SELECT constant_name,column_name
   FROM   user_cons_columns
   WHERE  table_name='EMP';

3)禁用约束
  语法：
   ALTER TABLE teble
     DISABLE CONSTRAINT constraint [CASCADE];
  其中：
       table----表名
       constraint----约束名
  注：既可以在CREATE TABLE也可以在ALTER TABLE语句中使用DISABLE子句
      CASCADE子句禁用想依赖的完整性约束

  例：ALTER TABLE test_p
        DISABLE CONSTRAINT test_deptno_pk CASCADE;
  -执行ALTER TABLE语句的DISABLE子句禁用完整性约束，可以禁用一个约束而不删除它
  -应用CASCADE选项禁用想依赖的完整性约束
  -禁用唯一键或主键会移除唯一性索引

4)启用约束
  语法：
   ALTER TABLE table 
     ENABLE CONSTRAINT constraint;

   例：ALTER TABLE test_p
         ENABLE CONSTRAINT test_deptno_pk;
  -若启用一个约束， 约束将应用于表中所有数据，所有表中数据都必须适合该约束
  -若启用一个UNIQE键或者PRIMARY KEY约束，一个UNIQUE或PRIMARY KEY索引将被自动地创建
  -既可以在CREATE TABLE也可以在ALTER TABLE语句中使用ENABLE子句
  -启用一个带CASCADE选项的被禁用的主键约束不会启用任何依赖于该主键的外键

5)删除约束
  语法：
  ALTER TABLE table
    DROP PRIMARY KEY | UNIQUE(column)|
      CONSTRAINT constraint [CASCADE];
  其中：
       table----表名
       column----受约束影响的列的名字
       constraint----约束的名字
       CASCADE选项导致任何与其相依赖的约束也被删除

  例1：从emp表中删除经理约束
      ALTER TABLE emp
        DROP CONSTRAINT emp_mgr_fk;

  例2：删除test_p表上的PRIMARY KEY约束，并删除相关联的在test_f.deptno列上的FOREIGN KEY约束
       ALTER TABLE test_p
         DROP PRIMARY KEY CASCADE;
 
  删除约束步骤：
  -从USER_CONSTRAINTS和USER_CONS_COLUMNS数据字典视图中确定约束的名字
  -使用带DROP子句的ALTER TABLE语句
  -DROP子句的CASCADE选项导致任何与其相依赖的约束也被删除
 
    
练习
1.创建test_f表
  列名  数据类型    长度    主键    空否
  id    NUMBER       7      YES
  name  VARCHAR2     25             NOT NULL

   CREATE TABLE test_f
          (id NUMBER(7),
           name VARCHAR2(25) NOT NULL,
           CONSTRAINT test_fid_pk PRIMARY KEY(id));

2.创建test_p表
  列名       数据类型    长度    主键    空否
  deptno     NUMBER       8      YES  
  dept_name  VARCHAR2     25             NOT NULL

   CREATE TABLE test_p
          (deptno NUMBER(8),
           dept_name VARCHAR2(25) NOT NULL,
           CONSTRAINT test_pno_pk PRIMARY KEY(deptno));

3.更改test_f表，增加deptno字段，类型NUMBER，长度8
 
    ALTER TABLE test_f
     ADD (deptno NUMBER(8));

4.添加一个外键到test_f表的deptno列上，该外键参照test_P表的deptno列，将该约束命名为my_dept_id_fk

   ALTER TABLE test_f
     ADD CONSTRAINT my_dept_id_fk
         FOREIGN KEY(deptno)
         REFERENCES test_p(deptno);

5.更改test_f表，增加sal字段，类型为NUMBER，长度为7，增加check约束，要求sal>1000
  
   ALTER TABLE test_f
     ADD (sal NUMBER(7),
          CONSSTRAINT test_fsal_c
          CHECK (sal>100));

6.查询USER_CONSTRAINTS视图，确认约束已被添加，注意约束类型和名字

   SELECT table_name,constraint_name,constraint_type,status
   FROM   user_constraints
   WHERE  table_name='TEST_F';

7.禁用test_f表的check约束

   ALTER TABLE test_f
     DISABLE CONSTRAINT test_fsal_c CASCADE;

8.启用test_f表的check约束

   ALTER TABLE test_f
     ENABLE CONSTRAINT test_fsal_c CASCADE;

9.删除test_f表的check约束

   ALTER TABLE test_f
      DROP CONSTRAINT test_fsal_c CASCADE;