« 国内数据库启蒙导师 人大萨师煊老师仙逝 | Blog首页 | [招聘信息] - 万达院线招聘DBA一名 »

关于Oracle Check类型约束的导入与启用

今天有朋友问，Oracle的Check约束在indexfile中是否存在，导入后没有检查到。
我测试了一下，事实证明是可以的，Oracle的Check Constraints可以通过imp，使用indexfile来获得，并且可以通过手工执行脚本来加载。

以下是一个简单的测试过程。创建测试表：

[oracle@oracle ~]$ sqlplus EYGLE/EYGLE

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jul 13 23:48:35 2010

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

SQL> CREATE TABLE suppliers
  2  (  supplier_id     number(4),
  3     supplier_name   varchar2(50),
  4     CONSTRAINT check_supplier_id
  5     CHECK (supplier_id BETWEEN 100 and 9999)
  6  );

Table created.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

导出数据，生成indexfile:

[oracle@oracle ~]$ cd /tmp
[oracle@oracle tmp]$ exp EYGLE/EYGLE file=c.dmp tables=suppliers

Export: Release 10.2.0.5.0 - Production on Tue Jul 13 23:49:31 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                      SUPPLIERS          0 rows exported
Export terminated successfully without warnings.

[oracle@oracle tmp]$ imp EYGLE/EYGLE file=c.dmp indexfile=a.txt full=y

Import: Release 10.2.0.5.0 - Production on Tue Jul 13 23:49:56 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . skipping table "SUPPLIERS"                       

Import terminated successfully without warnings.

indexfile中包含了完整的语句，去掉注释执行后，可以正常创建所有的对象及约束：

[oracle@oracle tmp]$ more a.txt

REM  CREATE TABLE "EYGLE"."SUPPLIERS" ("SUPPLIER_ID" NUMBER(4, 0),
REM  "SUPPLIER_NAME" VARCHAR2(50)) PCTFREE 10 PCTUSED 40 INITRANS 1
REM  MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 FREELISTS 1 FREELIST
REM  GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EYGLE" LOGGING NOCOMPRESS ;
REM  ... 0 rows
REM  ALTER TABLE "EYGLE"."SUPPLIERS" ADD CONSTRAINT "CHECK_SUPPLIER_ID"
REM  CHECK (supplier_id BETWEEN 100 and 9999) ENABLE NOVALIDATE ;
REM  ALTER TABLE "EYGLE"."SUPPLIERS" ENABLE CONSTRAINT
REM  "CHECK_SUPPLIER_ID" ;
[oracle@oracle tmp]$ sqlplus EYGLE/EYGLE

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jul 13 23:50:28 2010

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options

SQL> drop table suppliers purge;

Table dropped.

SQL>  CREATE TABLE "EYGLE"."SUPPLIERS" ("SUPPLIER_ID" NUMBER(4, 0),
  2   "SUPPLIER_NAME" VARCHAR2(50)) PCTFREE 10 PCTUSED 40 INITRANS 1
  3   MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 FREELISTS 1 FREELIST
  4   GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EYGLE" LOGGING NOCOMPRESS ;

Table created.

SQL>  ALTER TABLE "EYGLE"."SUPPLIERS" ADD CONSTRAINT "CHECK_SUPPLIER_ID"
  2   CHECK (supplier_id BETWEEN 100 and 9999) ENABLE NOVALIDATE ;

Table altered. 

SQL>  ALTER TABLE "EYGLE"."SUPPLIERS" ENABLE CONSTRAINT "CHECK_SUPPLIER_ID" ;


Table altered.

SQL> select table_name,constraint_name,constraint_type from user_constraints where table_name='SUPPLIERS';

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
SUPPLIERS                      CHECK_SUPPLIER_ID              C

SQL> drop table SUPPLIERS purge;

Table dropped.

记录供参考。

-The End-

By eygle on 2010-07-14 01:02 | Comments (0) | FAQ | 2581 |

Constraint