CONSTRAINT clause

Purpose

To define an integrity constraint. An integrity constraint is a rule that restricts the values for one or more columns in a table or an index-organized table.

Prerequisites

CONSTRAINT clauses can appear in either CREATE TABLE or ALTER TABLE commands. To define an integrity constraint, you must have the privileges necessary to issue one of these commands. See CREATE TABLE and ALTER TABLE .

Defining a constraint may also require additional privileges or preconditions, depending on the type of constraint. For information on these privileges, see the descriptions of each type of integrity constraint in "Defining Integrity Constraints".

Syntax

table_constraint::=

column_constraint::=

storage_clause: See the STORAGE clause .

CREATE TABLE dept  (deptno NUMBER  CONSTRAINT check_deptno
          CHECK (deptno BETWEEN 10 AND 99) 
          DISABLE, 
dname VARCHAR2(9)  CONSTRAINT check_dname 
          CHECK (dname = UPPER(dname)) 
          DISABLE, 
loc VARCHAR2(10)  CONSTRAINT check_loc 
          CHECK (loc IN ('DALLAS','BOSTON',
          'NEW YORK','CHICAGO')) 
          DISABLE);

CREATE TABLE

Syntax

Relational table definition ::=

Object table definition ::=

column_ref_clause::=

table_ref_clause::=

segment_attributes_clause::=

physical_attributes_clause::=

storage_clause: See the STORAGE clause .

Examples

Example I

To define the EMP table owned by SCOTT, you could issue the following statement:

CREATE TABLE scott.emp 
   (empno     NUMBER        CONSTRAINT pk_emp PRIMARY KEY, 
    ename     VARCHAR2(10)  CONSTRAINT nn_ename NOT NULL 
                            CONSTRAINT upper_ename 
CHECK (ename = UPPER(ename)), 
    job        VARCHAR2(9), 
    mgr      NUMBER         CONSTRAINT fk_mgr 
                            REFERENCES scott.emp(empno), 
    hiredate  DATE          DEFAULT SYSDATE, 
    sal       NUMBER(10,2)  CONSTRAINT ck_sal 
CHECK (sal > 500), 
       comm      NUMBER(9,0)   DEFAULT NULL, 
       deptno    NUMBER(2)     CONSTRAINT nn_deptno NOT NULL 
                               CONSTRAINT fk_deptno 
                               REFERENCES scott.dept(deptno) ) 
PCTFREE 5 PCTUSED 75;

This table contains 8 columns. The EMPNO column is of datatype NUMBER and has an associated integrity constraint named PK_EMP. The HIRDEDATE column is of datatype DATE and has a default value of SYSDATE, and so on.

This table definition specifies a PCTFREE of 5 and a PCTUSED of 75, which is appropriate for a relatively static table. The definition also defines integrity constraints on some columns of the EMP table.

Example II

To define the sample table SALGRADE in the HUMAN_RESOURCE tablespace with a small storage capacity and limited allocation potential, issue the following statement:

CREATE TABLE salgrade 
   ( grade  NUMBER  CONSTRAINT pk_salgrade 
                    PRIMARY KEY 
                    USING INDEX TABLESPACE users_a, 
     losal  NUMBER, 
     hisal  NUMBER ) 
TABLESPACE human_resource 
STORAGE (INITIAL     6144  
         NEXT        6144 
         MINEXTENTS     1  
         MAXEXTENTS     5 
         PCTINCREASE    5);

The above statement also defines a PRIMARY KEY constraint on the GRADE column and specifies that the index Oracle creates to enforce this constraint is created in the USERS_A tablespace.

For more examples of defining integrity constraints, see the CONSTRAINT clause . For examples of enabling and disabling integrity constraints, see the ENABLE clause and the DISABLE clause .

Example III

When using parallel query, the fastest way to create a table that has the same columns as the EMP table, but only for those employees in department 10, is to issue a command similar to the following:

CREATE TABLE emp_tmp
   NOLOGGING
   PARALLEL (DEGREE 3)
   AS SELECT * FROM emp WHERE deptno = 10;

Using parallelism speeds up the creation of the table because Oracle uses three processes to create the table. After the table is created, querying the table is also faster, because the same degree of parallelism is used to access the table.

LOB Column Example

The following example creates table LOB_TAB with two LOB columns and specifies the LOB storage characteristics:

CREATE TABLE lob_tab (col1 BLOB, col2 CLOB)
STORAGE (INITIAL 256 NEXT 256)
LOB (col1, col2) STORE AS
   (TABLESPACE lob_seg_ts
    STORAGE (INITIAL 6144 NEXT 6144)
    CHUNK 4
    NOCACHE LOGGING
       INDEX (TABLESPACE lob_index_ts
       STORAGE (INITIAL 256 NEXT 256)
   )
);

Index-Organized Tables

Index-organized tables differ from other kinds of tables in that Oracle maintains the table rows in a B*-tree index built on the primary key. However, the index row contains both the primary key column values and the associated non-key column values for the corresponding row.

Example

The following statement creates an index-organized table:

CREATE TABLE docindex
  ( token CHAR(20),
    doc_oid INTEGER,
    token_frequency SMALLINT,
    token_occurrence_data VARCHAR(512),
       CONSTRAINT pk_docindex PRIMARY KEY (token, doc_oid) )
  ORGANIZATION INDEX TABLESPACE text_collection
  PCTTHRESHOLD 20 INCLUDING token_frequency
  OVERFLOW TABLESPACE text_collection_overflow;