ORACLE中约束的禁用和启用

婉9843

查看表TEST_PHONE_TAB的元数据：
SQL> select dbms_metadata.get_ddl('TABLE','TEST_PHONE_TAB') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','TEST_PHONE_TAB')
--------------------------------------------------------------------------------
  CREATE TABLE "SCOTT"."TEST_PHONE_TAB"
   (    "MOBILE" VARCHAR2(15),
        "SENDCOUNT" NUMBER(20,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DUQ_TEST_TBS01"

建立主键(包含一个同名索引和一个同名唯一约束)
已用时间:  00: 00: 00.31
SQL> alter table test_phone_tab add constraint test_phone_pk primary key(mobile);
表已更改。
已用时间:  00: 00: 00.01
查看修改表后的元数据：
SQL> select dbms_metadata.get_ddl('TABLE','TEST_PHONE_TAB') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','TEST_PHONE_TAB')
--------------------------------------------------------------------------------
  CREATE TABLE "SCOTT"."TEST_PHONE_TAB"
   (    "MOBILE" VARCHAR2(15),
        "SENDCOUNT" NUMBER(20,0),
         CONSTRAINT "TEST_PHONE_PK" PRIMARY KEY ("MOBILE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEX

已用时间:  00: 00: 00.26
查看该表的索引的元数据
SQL> select dbms_metadata.get_ddl('INDEX','TEST_PHONE_PK') FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX','TEST_PHONE_PK')
--------------------------------------------------------------------------------
  CREATE UNIQUE INDEX "SCOTT"."TEST_PHONE_PK" ON "SCOTT"."TEST_PHONE_TAB" ("MOBILE")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

已用时间:  00: 00: 00.23
禁用约束：test_phone_pk
SQL> ALTER TABLE test_phone_tab disable constraint test_phone_pk;
表已更改。
已用时间:  00: 00: 00.03
禁用约束test_phone_pk后，再次查看该约束的元数据。但数据字典中已经没有了
该索引。证明如果约束有索引，在禁用约束的时候，会自动删除其对应的索引。
SQL> select dbms_metadata.get_ddl('INDEX','TEST_PHONE_PK') FROM DUAL;
ERROR:
ORA-31603: 对象 "TEST_PHONE_PK" 属于类型 INDEX, 在方案 "SCOTT" 中未找到
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: 在 "SYS.DBMS_METADATA", line 2697
ORA-06512: 在 "SYS.DBMS_METADATA", line 4220
ORA-06512: 在 line 1

未选定行
已用时间:  00: 00: 00.45
查看禁用约束后的表的元数据。
此时约束‘TEST_PHONE_PK’的状态为‘DISABLE’。即：禁用。
SQL> select dbms_metadata.get_ddl('TABLE','TEST_PHONE_TAB') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','TEST_PHONE_TAB')
--------------------------------------------------------------------------------
  CREATE TABLE "SCOTT"."TEST_PHONE_TAB"
   (    "MOBILE" VARCHAR2(15),
        "SENDCOUNT" NUMBER(20,0),
         CONSTRAINT "TEST_PHONE_PK" PRIMARY KEY ("MOBILE") DISABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DUQ_TEST_TBS01"

已用时间:  00: 00: 00.21
SQL> ALTER TABLE test_phone_tab enable constraint test_phone_pk;
表已更改。
已用时间:  00: 00: 00.01
查看启用约束后的表的元数据。
此时约束‘TEST_PHONE_PK’的状态为已不为‘DISABLE’。即：启用。
SQL> select dbms_metadata.get_ddl('TABLE','TEST_PHONE_TAB') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','TEST_PHONE_TAB')
--------------------------------------------------------------------------------
  CREATE TABLE "SCOTT"."TEST_PHONE_TAB"
   (    "MOBILE" VARCHAR2(15),
        "SENDCOUNT" NUMBER(20,0),
         CONSTRAINT "TEST_PHONE_PK" PRIMARY KEY ("MOBILE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEX

已用时间:  00: 00: 00.28
启用约束后，其对应的索引已自动创建。
SQL> select dbms_metadata.get_ddl('INDEX','TEST_PHONE_PK') FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX','TEST_PHONE_PK')
--------------------------------------------------------------------------------
  CREATE UNIQUE INDEX "SCOTT"."TEST_PHONE_PK" ON "SCOTT"."TEST_PHONE_TAB" ("MOBILE")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
已用时间:  00: 00: 00.09
找到违反约束条件的行
创建exceptions表。
1.执行脚本：
@%ORACLE_HOME%\RDBMS\ADMIN\utlexpt1.sql
2.SQL> create table tt1 as select * from emp;

表已创建。
已用时间:  00: 00: 00.12
3.创建一个未启用的主键：
SQL> alter table tt1 add constraint tt1_pk primary key(empno)disable;表已更改。
4.修改表中数据，让其有重复记录。
已用时间:  00: 00: 00.00
SQL> update tt1 set empno=7900 where empno=7902;

已更新 1 行。
已用时间:  00: 00: 00.00
SQL> commit;
提交完成。
已用时间:  00: 00: 00.01
5.启用主键约束，并让违反约束条件的行插入表exceptions中。
SQL> alter table tt1 enable constraint tt1_pk exceptions into exceptions;
alter table tt1 enable constraint tt1_pk exceptions into exceptions
*
第 1 行出现错误:
ORA-02437: 无法验证 (SCOTT.TT1_PK) - 违反主键

已用时间:  00: 00: 00.03
SQL> select * from exceptions;
ROW_ID
---------------------------------------------------------------------------------------------------------
OWNER                          TABLE_NAME                     CONSTRAINT
------------------------------ ------------------------------ ------------------------------
AAAMeQAAEAAAAHkAAM
SCOTT                          TT1                            TT1_PK
AAAMeQAAEAAAAHkAAL
SCOTT                          TT1                            TT1_PK

已用时间:  00: 00: 00.01
6.查看违反约束条件的记录的详细信息
SQL> select * from tt1 where rowid in(
2  select row_id from exceptions);
EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7900 JAMES      CLERK           7698 03-12月-81            950                    30
7900 FORD       ANALYST         7566 03-12月-81           3000                    20
已用时间:  00: 00: 00.01