今天有朋友問(wèn)，Oracle的Check約束在indexfile中是否存在，導(dǎo)入后沒(méi)有檢查到。

我測(cè)試了一下，事實(shí)證明是可以的，Oracle的Check Constraints可以通過(guò)imp，使用indexfile來(lái)獲得，并且可以通過(guò)手工執(zhí)行腳本來(lái)加載。

以下是一個(gè)簡(jiǎn)單的測(cè)試過(guò)程。創(chuàng)建測(cè)試表：

[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導(dǎo)出數(shù)據(jù)，生成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中包含了完整的語(yǔ)句，去掉注釋執(zhí)行后，可以正常創(chuàng)建所有的對(duì)象及約束：

[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-

總結(jié)

以上是生活随笔為你收集整理的oracle中check约束性别,关于Oracle Check类型约束的导入与启用的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。