0702-0705

Previous Table of Contents Next

Page 702

Table Constraints

Each of the column constraints described in the preceding section also can be applied as table constraints, with the exception of NOT NULL. Table constraints have the additional advantage of being able to operate on multiple columns .

Refer to the CHECK constraint in Listing 28.6. This provides a sanity check on the lower bounds of date_of_birth, but it would be better to check the upper bounds as well. Listing 28.10 shows how you can accomplish this task by using a table constraint.

Listing 28.10. This DDL script, containing a CHECK table constraint, is an improvement on the column constraint used in Listing 28.6.

 CREATE TABLE individual (      ID              NUMBER(10)                      CONSTRAINT indiv_pk          PRIMARY KEY     ,last_name       VARCHAR2(30)                      CONSTRAINT indiv_l_name      NOT NULL     ,first_name      VARCHAR2(30)                      CONSTRAINT indiv_f_name      NOT NULL     ,notes           VARCHAR2(255)     ,date_of_birth   DATE     ,last_updt_user  VARCHAR2(20)                      CONSTRAINT indiv_lu_id NOT NULL     ,last_updt_date  DATE                      CONSTRAINT indiv_lu_dt NOT NULL     ,CONSTRAINT indiv_chk_bday          CHECK (date_of_birth BETWEEN (TO_DATE('01JAN1847', `DDMONYYYY', `nls_date_language = American'))                 AND last_updt_date) ); 

The syntax of table constraints is nearly identical to that of column constraints, except that the CONSTRAINT clause comes at the end of the column list.

Another advantage of using a table constraint applies only to constraints that result in the creation of an index. The table constraint syntax allows the designation of a tablespace and storage specification for the corresponding index. When indexes are created automatically for column constraints, they are created in the default tablespace using the default storage specification. The script in Listing 28.11 adds a UNIQUE constraint to the ind_co_rel table, enforcing uniqueness in the combination of individual_id and company_id. This listing also implements the USING INDEX clause to designate a tablespace and storage specification for the index that will be created.

Listing 28.11. This DDL script uses the ALTER TABLE syntax to create a UNIQUE table constraint and a storage specification for the corresponding index.

 ALTER TABLE ind_co_rel ADD CONSTRAINT ind_co_rel_u UNIQUE(individual_id, company_id) 

Page 703

 USING INDEX     TABLESPACE oracle_unleashed     STORAGE (INITIAL     10K              NEXT        10K              MAXEXTENTS  100              PCTINCREASE  10); 

The syntax for tablespace and storage specification following the USING INDEX keyword is identical to that for the CREATE INDEX statement. The USING INDEX clause also can be used in a PRIMARY KEY constraint, as shown in Listing 28.12.

Listing 28.12. This DDL script removes the UNIQUE constraint created in Listing 28.11 and re-creates it as a PRIMARY KEY.

 ALTER TABLE ind_co_rel DROP CONSTRAINT ind_co_rel_u / ALTER TABLE ind_co_rel DROP CONSTRAINT indco_ind_nn / ALTER TABLE ind_co_rel DROP CONSTRAINT indco_co_nn / ALTER TABLE ind_co_rel ADD CONSTRAINT ind_co_rel_pk PRIMARY KEY(individual_id, company_id)     USING INDEX     TABLESPACE oracle_unleashed     STORAGE (INITIAL     10K              NEXT        10K              MAXEXTENTS  100              PCTINCREASE  10) / 

Notice that the NOT NULL constraints on the individual_id and company_id columns also were dropped. Keeping these constraints would be redundant, because columns included in a PRIMARY KEY constraint cannot have NULL values.

Foreign key table constraints are implemented in much the same way as REFERENCING column constraints. If necessary, multiple columns can be included in a FOREIGN KEY table constraint. This is not the case with the column constraint implementation.

In Oracle8, foreign key constraint checking can be deferred, allowing otherwise failing transactions to complete successfully. Constraints are defined by using the keyword DEFERRABLE or NONDEFERRABLE (the default). Deferrable constraints are INITIALLY DEFERRED or INITIALLY IMMEDIATE, allowing the behavior of deferred constraints to be controlled at the session level.

Page 704

Deferred constraint checking can simplify the development of complex transactions with numerous dependencies and can increase transaction throughput in some cases. The disadvantage of this approach is that it allows processing to continue until a commit is issued. This method can waste processing time for long transactions that fail because of problems in early statements, and it can make it difficult to isolate the offending statement or statements.

Note also that the FOREIGN KEY keyword is available only for the table constraint syntax. Listing 28.13, the final script for ind_co_rel, also defines temporary tables into which exceptions are logged.

Listing 28.13. The final ind_co_rel script uses the FOREIGN KEY table constraint syntax and logs exceptions into temporary tables.

 CREATE TABLE ind_co_rel (      individual_id   NUMBER(10)     ,company_id      NUMBER(10)     ,title           VARCHAR2(80)     ,last_updt_user  VARCHAR2(20)                      CONSTRAINT indco_lu_id  NOT NULL                      EXCEPTIONS INTO ind_co_err_1     ,last_updt_date  DATE                      CONSTRAINT indco_lu_dt  NOT NULL                      EXCEPTIONS INTO ind_co_err_2     ,CONSTRAINT indco_ind_fk FOREIGN KEY (individual_id)           REFERENCES individual(ID)           ON DELETE CASCADE           EXCEPTIONS INTO ind_co_err_3     ,CONSTRAINT indco_co_fk  FOREIGN KEY (company_id)           REFERENCES company(ID)           ON DELETE CASCADE           EXCEPTIONS INTO ind_co_err_4     ,CONSTRAINT ind_co_rel_pk PRIMARY KEY(individual_id, company_id)           USING INDEX           TABLESPACE oracle_unleashed           STORAGE (INITIAL     10K                    NEXT        10K                    MAXEXTENTS  100                    PCTINCREASE  10)           EXCEPTIONS INTO ind_co_err_5 ) TABLESPACE oracle_unleashed; 

Each type of exception is logged into a different temporary table in Listing 28.13, which is helpful in debugging and resolving integrity problems. The tables into which exceptions are logged should be identical to the ind_co_rel table, except that they should have no constraints, for obvious reasons. You can use the EXCEPTIONS INTO clause for any type of column or table constraint. If the table referenced by EXCEPTIONS INTO does not exist, using the clause produces an error.

The EXCEPTIONS INTO clause is useful particularly in applications in which frequent and timely bulk loads are required. To use SQL*Loader with the Direct Path option to maximize its

Page 705

performance, constraints must be disabled. When the constraints are re-enabled, the EXCEPTIONS INTO clause can be used to categorize problem records by inserting them into separate tables based on the constraint that was violated. This process minimizes the negative impact constraints have on performance while maintaining integrity and providing a means of identifying and resolving problems with the data being loaded.

As shown in these examples, Oracle provides a wide variety of options for enforcing integrity through column and table constraints. Constraints are powerful tools for enforcing integrity, but you should use them with care. Overusing constraints can add significantly to long- term maintenance requirements, and misuse can create unwanted dependencies or unnecessary exceptions. The possible trade-offs involving constraints and performance are discussed in greater detail in the section "Using Triggers."

Using Sequences

Oracle sequences are ideally suited to the task of generating unique key values. A sequence is a stored object that simply generates a sequence of numbers . Listing 28.14 shows the syntax for creating a sequence.

Listing 28.14. This sequence is used to generate primary keys for the individual table.

 CREATE SEQUENCE individual_ids     START WITH 1     INCREMENT BY 1     NOMAXVALUE; 

The values specified for the START WITH and INCREMENT BY parameters in Listing 28.14 are the defaults. NOMAXVALUE is the default as well. The script in Listing 28.14 produces the same result as the following:

 CREATE SEQUENCE individual_ids; 

It is a good practice to explicitly declare these defaults for documentation purposes, if nothing else. The implementation is fairly self-explanatory. The START WITH parameter indicates the first number that will be generated, INCREMENT BY specifies a number to be added to the current value to generate the next value, and NOMAXVALUE indicates that there is no maximum to the numbers it will generate (practically no maximum, although there is an upper limit).

The MINVALUE parameter is used only by descending sequences. (Specifying a negative value for INCREMENT BY produces a descending sequence.) CYCLE, when used with MAXVALUE or MINVALUE, indicates that the sequence should start from the beginning when the minimum or maximum value is reached. The default for this option is NOCYCLE.

The CACHE parameter indicates the number of values that should be pregenerated and cached by the sequence. The default value for CACHE is 20. Raising this parameter can improve performance in high transaction volume environments.

Previous Table of Contents Next