' + pPage + ''; zhtm += ''; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //-->

Using Oracle8

Chapter 17

Using Constraints to Improve Your Application Performance

• Understanding Constraints
• Constraint Naming Conventions
  • Using Oracle's Naming Scheme
  • Developing Your Own Naming Schemes
• Creating and Managing Constraints
• General Constraint Options
  • Statement or Transaction Enforcement
  • Disabling and Enabling Constraints
• Forcing Input with the NOT NULL Constraint
• Ensuring Distinct Values with Unique Constraints
• Creating Distinct Rows with Primary Key Constraints
• Validating Data Against Existing Records with Foreign Key Constraints
• Defining Business Rules with Check Constraints
• Including Constraints in a New Table Definition
• Adding a Constraint to an Existing Table
• Modifying and Dropping Constraints
• Using Default Column Values

• Enforce business rules in your database efficiently
• Leverage consistent naming conventions
• Activate constraints selectively for different workloads
• Manage constraint dependencies
• Assign default values to columns

Understanding Constraints

Constraints allow you to define certain characteristics for columns in a table along with the table definition. In effect, they allow you to encode business rules about the data allowed in a table in addition to the table definition. Such business rules could include ensuring that no two customers are assigned the same ID number, or preventing an order from being taken without a customer ID number.

You should plan to use constraints whenever characteristics of a column or group of columns can't be enforced by the chosen datatype. For example, defining a column to hold the minutes portion of a time stamp as NUMBER(2) wouldn't prevent a user from entering a value of 75, even though this wouldn't be a valid number of minutes. You could, however, define a constraint on this MINUTES column to limit the range of values to be between 0 and 59, inclusive. When in effect, the constraint would prevent INSERT and UPDATE statements from creating a record with a value outside the required range in this MINUTES column.

Constraints provide efficiencies for application developers as well as for database operations:

• Simple to code as part of a table definition
• Easy to disable and re-enable as needed
• Always in place, regardless of tools used to access the table
• More or equally efficient execution compared to equivalent SQL statements
• Defined in data dictionary for centralized management
• Constraint names appear in exception messages
• Have to test only when created, not with each application

By using constraints, you reduce the work needed to enforce the related business rules. First, you save application developers a lot of work because they won't need to code an additional check to determine whether the provided value is within the acceptable range. In certain cases-such as ensuring that a value in a column is unique-this check could require coding an SQL statement to query the existing table entries.

This, in turn, provides the second benefit-fewer SQL statements improve your database efficiency because the database will have less work to do.

A third benefit of constraints is that they're part of the definition of the schema to which they belong, and as a result, comprise part of database exports. Naturally, this results in the constraints being passed between database structures when using the Export/Import functions.

In some ways, constraints are to relational tables as methods are to objects-both are integral to each other. As with a good object tool, Oracle8 attempts to process the constraint definitions in memory without generating additional, recursive SQL.

Constraints don't just define the characteristics of a single column, going beyond the type and length of data defined. They can include multiple columns when necessary. Further, you can define the implicit relationships between tables by using constraints. This way, you can maintain integrity between parent and child tables.

Constraint Naming Conventions

When you create a constraint, you can allow Oracle to name it for you or you can choose your own name. I recommend choosing your own names even though it requires more work for you during the design and development phases. Oracle's naming convention doesn't take into account your table's name or the columns to which the constraint refers. This makes it difficult for you to remember the names of the constraints that you create. The default name is even less useful when it's included in exception messages related to the constraint. Neither the user who receives the message nor the person responsible for analyzing the problem can determine the nature of the problem from an Oracle-generated constraint name.

Using Oracle's Naming Scheme

Every constraint in an Oracle database must have a name that's unique within its schema. If you don't provide one, Oracle will choose a name for you. These default names have the format SYS_Cn, where n is a five-digit integer with leading zeroes. For example, a constraint could have the name SYS_C00512.

Suppose that you have a constraint on the MINUTES column of a table that allowed only valid minute values (0 through 59), and the system had given this constraint the name SYS_C00512. A user attempting to enter a value of 75 into this column would receive an error message naming the constraint that they violated. Imagine how useful the user would find that error message, stating only that constraint SYS_C000512 had been violated. How would the user, or anyone in a support position, determine the nature of the error from that message?

Along the same lines, suppose that you decided to store the fraction of an hour in the minutes column rather than the actual number of minutes-30 minutes would now be stored as 50, representing 50 percent of an hour. To save a value representing 52 minutes, you would now need to store 86. To allow this value, you would have to disable the constraint restricting the column to a high value of 59. You can find the constraints placed on a table through the view DBA_CONSTRAINTS (or USER_CONSTRAINTS). Looking at the entries in this table, though, it would be difficult for you to determine that constraint SYS_C000512 is the one you need to remove.

Another situation in which you may need to quickly identify a constraint's source is when you're dealing with constraint dependencies. It's very unlikely that you would remember which Oracle-generated name was given to the primary key constraint on a table you created a couple of months ago, but you may need to know when creating another table so that you can verify the constraint's definition. Similarly, if you were to change the status of that primary key constraint, you might be told that your newer constraint was dependent on its current status. Would you know to which constraint the message was referring if it gave you only the name SYS_C00512?

Developing Your Own Naming Schemes

You could adopt a number of possible naming schemes for identifying constraints. Most of them contain three elements in the name: table name, column name, and constraint type. Of course, this may not always be possible if you use long names for either (or both) table and column names. The constraint name is limited to the 30-character maximum set for all Oracle names. If your table name is already 25 characters long, this leaves only five characters for both column name and constraint type. You may therefore need to define a standard set of abbreviations for your table and column names.

The reason to use all three elements should be obvious from the preceding section. If you want to drop or modify a constraint on the values allowed in a MINUTES column in a table, you would like to be able to reference the constraint name directly in the SQL statements needed to effect the change. The most complete naming convention would identify the table name, the column name (MINUTES), and the type of constraint.

As schema objects, your constraints must have names that are unique within their schema. If you used only table name and constraint type as a naming convention, you would limit yourself to just one constraint of each type in any table, or else you would have to differentiate the constraints with some other naming element. Many people gravitate to a numbering scheme when faced with this type of problem, but if you were to use this approach, you're likely to end up with constraint names such as ACTION_UQ_1 and ACTION_UQ_2. This is almost as useless for quick identification as is the infamous constraint named SYS_C00512.

There are only five constraint types; the sections devoted to each type appear later in this chapter. It's quite simple to assign a meaningful two-character string to identify each type. For example, UQ was used in the preceding paragraph. UQ is a widely used abbreviation for the unique constraint. Although this is jumping the gun, Table 17.1 shows the types of constraints, along with a suggested two-character abbreviation for use in your constraint names.

Besides deciding to use the three-part constraint names, you also need to determine in which order these parts appear in the name. You should be able to find five distinct abbreviations for the constraint type that won't be confused with either table or column names, so this isn't a major concern. However, it's possible for a table and a column (in the same or a different table) to have identical names. You could mistake constraints based on an ambiguous name unless you have a standard to ensure that these two elements are always included in the constraint name in the same order.

Another point to consider is the use of constraints that cover multiple columns. Consider a constraint name such as PATIENT_TREATMENT_ID_UQ. This could be a unique constraint on the ID column of the PATIENT_TREATMENT table, or on the combined TREATMENT and ID columns of the PATIENT table. You reduce this ambiguity if you separate the table and column names with the constraint-type abbreviation. Thus, the preceding example would become PATIENT_TREATMENT_UQ_ID or PATIENT_UQ_TREATMENT_ID, depending on the name of the base table.

Creating and Managing Constraints

Unlike other database objects that have their own CREATE, ALTER, and DROP commands, constraints are so closely tied to the tables they control that they're manipulated by the commands associated with those tables. As you may remember from Chapter 7 "Adding Segments for Tables," the syntax for creating and altering tables is quite complex in itself. The concepts and the syntax for dealing with constraints is also rather complicated. When you put the two together, the resulting syntax diagram becomes almost impossible to read. Consequently, as you look at each of the options associated with constraints, just the keywords or key phrases required by the syntax to manage that option are introduced. When you're comfortable with these concepts, this chapter ends with a look at the overall constraint syntax and you can see how it fits into the various table management commands.

In the next few sections you examine the options you can apply to constraints that in turn help you and your users make the most effective use of the options. Some options, such as the capability to temporarily turn off constraint checking, apply to all constraint types; other options are specific to one type of constraint. The latter are covered in individual sections devoted to each constraint type. Begin by looking at constraint options that include the options available to all constraints.

An overall syntax diagram for a constraint definition looks like this:

[CONSTRAINT constraint_name] constraint_type [constraint_options]

You were briefly introduced to constraint types in Table 17.1, and we will look at each type in detail later in this chapter. The options you look at first, as mentioned, are those valid for all constraint types.

General Constraint Options

In the following sections you look at the various options available to you regarding the enforcement of constraints. These options apply to all types of constraints; they are identified and given values in the segment of the constraint definition labeled constraint_options in the constraint syntax diagram.

When a constraint is defined, it stays associated with the table to which it applies. However, it may not always be convenient to have the constraint enforced all the time. You have various options to determine when new records, or even updated records, will be checked for validity against the relevant constraints. In some cases you may want to check as soon as a statement is processed; on the other hand, you may want to wait until a whole work batch is complete before identifying constraint violations.

To determine when and how to force a constraint check when each individual statement or transaction executes, you should read the next section, which discusses immediate and deferred constraint checking. To look at reasons for turning off constraints for an arbitrary period of time, read the "Disabling and Enabling Constraints" section.

Statement or Transaction Enforcement

When a constraint is in place, every row affected by an SQL statement is examined for constraint violations, and an exception is raised if at least one row remains in violation when the statement completes. This behavior allowsinterim violations to exist for the duration of the statement, but not beyond. An interim violation is one that, although breaking the rules of constraint while processing is under way, is revolved by the time the processing completes. As a simple example, consider a table containing a company's department information. The departments are numbered one by one, but due to a reorganization, the company needs to make the numbers multiples of 10; that allows new subdepartments to be added in the gaps. A partial query of the DEPARTMENTS table's original contents and the UPDATE statement needed to make the required change are shown in this SQL*Plus session:

SQL> REM    Examine current values in DEPARTMENT_ID column of
SQL> REM    DEPARTMENTS table
SQL> SELECT department_id
     2   FROM departments
     3   WHERE department_id <= 20
     4   ORDER BY department_id
/
DEPARTMENT_ID
                              1
                              2
                              3
                              4
                              5
                              6
                              7
                              8
                              9
                             10
                             11
                             12
                             13
                             14
                             15
                             16
                             17
                             18
                             19
                             20
20 rows selected.
REM    Multiply each DEPARTMENT_ID by 10 to
REM    change increment from 1 to 10
SQL> UPDATE department
     2   SET department_id = department_id * 10
/
65 rows updated.
REM    Check the work
SQL> SELECT department_id
     2   FROM departments
     3   WHERE department_id <= 20
     4   ORDER BY department_id
/
DEPARTMENT_ID
                            10
                            20
SQL>

At some point during the execution of this UPDATE statement, the old DEPARTMENT_ID 1 became 10, the old DEPARTMENT_ID 2 became 20, and so on. If the DEPARTMENTS table had a constraint to prevent duplicate DEPARTMENT_ID values at the time these changes were made, there would have been constraint violations, because the original department 1 and the original department 10 both would have a value of 10 in their DEPARTMENT_ID columns. This would also have been the case for departments 2 and 20, departments 3 and 30, and on up to departments 6 and 60. If Oracle caused the statement to fail due to these anomalies, it would be very difficult for users to make these perfectly valid, albeit infrequent, changes. To avoid this, Oracle marks the duplicates as interim violations and then, when the statement has completed all its changes, it checks them again to see if they remain as violations. By this time, these changes would have included changing the original departments 10, 20, 30… to 100, 200, 300…, hence removing duplicate values for 10, 20, 30…. The statement can therefore complete without any problems.

By default, Oracle checks interim constraint violations at the end of each statement. If one or more are found, the statement fails with an exception and all the changes are rolled back. In some cases, you may want to delay the constraint checking because you need to combine the effects of two or more statements to create consistent records.

Suppose, based on the preceding example, that employees are associated with each DEPARTMENT_ID value in the DEPARTMENTS table. Oracle can enforce a rule (by using a constraint type that's discussed later) that all employees must have a valid department number as part of their records. If we make the preceding change to the DEPARTMENTS table, any employee registered to departments other than 10, 20, 30, 40, 50, or 60 would have an invalid record. To correct this, we would also have to change the employee records. This would take a second statement-thus, the default behavior is of no use because the first statement, the one changing the DEPARTMENTS table, would fail. Changing the employee records first wouldn't help either because we would need to change employees in department 65 (for example) to be in department 650, and such a department number doesn't exist in the unchanged DEPARTMENTS table.

To complete the required changes to both tables-the DEPARTMENTS table and the one with the employee records-we need to defer the constraint checking until the department numbers are changed in both. This is done by using what are known as deferred constraints. If you expect to use deferred constraints for any reason, you must understand what options are available. There are basically two approaches to deferring constraints: one requiring the application user to defer any required constraints at the time the transaction begins, and one allowing the constraint to be deferred automatically in all transactions. Within the constraint definition options, these activities are controlled with the following keywords:

• DEFERRABLE
• NOT DEFERRABLE
• INITIALLY IMMEDIATE
• INITIALLY DEFERRED

The DEFERRABLE keyword determines whether the constraint can be deferred at the time the transaction begins. A constraint can't be deferred by an application when defined as NOT DEFERRRABLE. When defined as DEFERRABLE, the following SQL command will allow any interim violations of the named constraint to remain until the transaction is committed, regardless of when the statement that caused the violations occurred within the transaction:

SET CONSTRAINT constraint_name DEFERRED

You can make multiple constraints deferrable with the SET CONSTRAINT command by naming them in a comma-separated list. Alternatively, you can issue the following command to defer checking all the constraints encountered in the transaction until it completes:

SET CONSTRAINT ALL DEFERRED

If you use a list of constraints in the command, they must all be defined as deferrable-otherwise the command will fail. If you use the keyword ALL, only the deferrable constraints, if any, will become deferrable for that transaction. The SET CONSTRAINT command is no longer in force as soon as the transaction completes with a COMMIT or a ROLLBACK.

The INITIALLY keyword sets a deferrable constraint's default behavior. If set to INITIALLY DEFERRED, a constraint is automatically deferred within any transaction that encounters it. You don't need to issue the SET CONSTRAINT command to defer checking on such a constraint until the end of a transaction; it will be done that way anyway. By default, however, a constraint is INITIALLY IMMEDIATE; this means that all interim violations are checked at the end of each statement and the statement will fail if any are found.

Because the INITIALLY keyword is valid only for constraints already defined as DEFERRABLE, the SET CONSTRAINT command can be used to override DEFERRED or IMMEDIATE. We have already seen that the following statement will defer checking the named constraint until the end of the transaction:

SET CONSTRAINT constraint_name DEFERRED

Similarly, this command will cause the named constraint to be checked at the end of each statement in which it's invoked during the course of the transaction, even if it's defined as INITIALLY DEFERRED:

SET CONSTRAINT constraint_name IMMEDIATE

In either case, the scope of the SET CONSTRAINT command is a single transaction; unless you reissue it at the start of your next transaction, the default behavior will apply to all constraints again.

One final note on deferred transactions: During such a transaction, you can issue the following command to see if any interim violations now exist:

SET TRANSACTION ALL IMMEDIATE

If they do, the command will return an error message about the violation, such as ORA-00001: unique constraint (SYS_C00315) violated or ORA-02292: integrity constraint (SYS_C00894) violated - child record found.

Disabling and Enabling Constraints

In the preceding section you saw how to defer constraint checking until the end of a transaction. Sometimes you might find it useful to disable a constraint for an even longer period. Suppose you're building a new medical database in which you have two tables, such as the DOCTORS and the PATIENTS tables, with dependent information. You might want to use a constraint to ensure that every patient is assigned to a doctor. (The section "Validating Data Against Existing Records with Foreign Key Constraints" covers this type of constraint, known as a foreign key or referential integrity constraint later in this chapter.) If the constraint to enforce this logical relationship between doctors and patients were enforced, you would have to load all doctors' records first and then all patients' records. This would prevent you from running multiple load programs, some storing patient data and some doctor data, to shorten the overall load time.

The data loading problem could become even more complex if the patient table included a field for the financially responsible party and forced a referential integrity check between patients and their financial representatives. If the dependent patient's record were loaded before the responsible party's record, there would be a constraint violation and the load would fail. Even deferred constraint checks couldn't help us if the records for the two related patient records were being stored through two different load programs; each program would be managing just its own transactions.

To help you with such situations, Oracle allows you to temporarily turn off a constraint and then to restart its enforcement later. These actions are known as constraint disabling and enabling, respectively.

Disabling Constraints

In general, there are a number of reasons for turning off constraint checking when performing large data loads, whether into a brand new database or adding to existing data. Rather than make you drop the entire constraint definition, Oracle lets you disable a constraint. The constraint remains defined in the data dictionary while it's disabled, but it's not enforced. You can re-enable a disabled constraint whenever you're ready. At this time, Oracle will check for any violations, and if it finds one or more, will return an exception message and leave the constraint disabled.

When you enable a constraint and expect (or already know) that there will at least violation, you can ask Oracle to save information about which row or rows are causing the exception in a special table. By using data from this table, you can extract the non-conforming rows into a temporary table for later inspection and resolution. With this done, you can then re-enable the constraint on the remaining rows, thus protecting the table from any further violations.

First look at the various ways you can disable a constraint. The methods include the following:

• Creating the constraint in a disabled state
• Disabling an enabled constraint by using the constraint type
• Disabling an enabled constraint by using the constraint name
• Explicitly disabling a dependent constraint

By default, all constraints are created in the enabled state. If you include the keyword DISABLE in the same clause as you define it, however, your constraint will be defined but disabled. To enforce such a constraint, you would have to enable it at a later time.

Certain types of constraints can be disabled by identifying just the constraint type, others by identifying the constraint type and the column or columns on which the constraint is defined. Either type, plus any other type of constraint, can be disabled if you know its name.

Because only one primary key constraint can be defined on a table, the syntax to disable it can be as simple as DISABLE PRIMARY KEY. The syntax that disables a unique constraint is almost as simple. In this case, the key phrase is DISABLE UNIQUE (column_name), which identifies the name of the column on which the constraint is defined. In some cases, the unique constraint will span multiple columns. In such cases, the parentheses need to contain not just one column name, but a list of the relevant column names separated by commas.

Primary-key and unique constraints, as well as any other constraint, can be disabled by naming them in the DISABLE CONSTRAINT constraint_name clause. Here, only one constraint can be named in the phrase. You would need to either enter multiple disable phrases or issue multiple SQL commands in order to disable more than one constraint on a table.

In some cases, a constraint may have another constraint that depends on its existence. You can't disable a constraint with such a dependency unless you also disable the dependent constraint. Although you can issue a separate command to disable the dependent constraint first, you can also use the DISABLE clause's CASCADE option to disable any dependent constraints at the same time you disable the parent constraint. DISABLE PRIMARY KEY CASCADE is an example of this type of statement.

One final issue that you should understand about disabling constraints concerns primary-key and unique constraints. Oracle uses an index to enforce these. If the index had to be built when the constraint was enabled, the disabling action will also drop the index. In addition to stopping the integrity checking, the loss of the index may reduce the performance of statements that could normally use the index to reduce the number of rows accessed.

Enabling Constraints

By default, constraints are enabled when they're first defined. As you've just seen, they can be created in a disabled state or disabled at a later time. You need to use one form of the ENABLE constraint clause whenever you need to make a disabled constraint active. In its simplest form, the ENABLE phrase resembles the DISABLE phrase discussed in the preceding section. That is, it can be in one of the following forms:

• ENABLE PRIMARY KEY
• ENABLE UNIQUE (column_name,[column_name][,...])

ENABLE CONSTRAINT constraint_name

These commands shouldn't have any problems executing if either no changes have been made to the table since the constraint was disabled or if the table is empty. On the other hand, if there are new or changed rows in the table, the constraint may not be re-enabled due to records that violate the constraint. If this is the case, you have two options:

• Reactivate the constraint with the ENABLE NOVALIDATE phrase rather than just the ENABLE phrase (which actually includes an implied VALIDATE keyword that you could code if you wanted). This will cause all further changes to be subject to the constraint but won't examine the existing data. If you use this approach, you may need to find a way to go back and correct the invalid rows at a later time.
• Identify the rows and deal with them, thus allowing the constraint to be enabled successfully. To do this, you need to add the phrase EXCEPTIONS INTO table_name to the ENABLE phrase. The table_name can be either just a table name or a schema and table name. Whatever table you use, it must be formatted in a specific way for the command to work.

If you use an exceptions table, any rows that violate a constraint when you try to enable it are identified in this table. Each such row has an entry in the EXCEPTIONS table showing its rowid, the name of the constraint that the row violates, and the name and owner of the table to which the row belongs. After you identify the rows that violate a constraint, you need to either update them to fix the problem or delete them, depending on the nature of the problem and the application's requirements. In many cases, you may want to defer the corrections until a later time but still want to enable the constraint to avoid further potential violations. The following script contains a series of commands that attempt to activate a unique constraint in the DOCTORS table and then moves any problem rows out of DOCTORS and into a temporary table, FOR_FIXING, where they can be processed later:

ALTER TABLE doctors
ENABLE UNIQUE (suite_number)
EXCEPTIONS INTO exceptions
/
CREATE TABLE for_fixing
    AS SELECT * FROM doctors
    WHERE rowid IN
        (SELECT row_id FROM exceptions)
/
DELETE doctors WHERE rowid IN
    (SELECT row_id FROM exceptions)
/
TRUNCATE TABLE exceptions
/
ALTER TABLE doctors
ENABLE UNIQUE (suite_number)

/

Unless additional invalid changes are made to the DOCTORS table between the time you start this script and the time the final command is executed, the constraint should be enabled when the script completes. When you or your users have updated the FOR_FIXING table created by the script, you can try to put the rows back into the original table with the following command:

INSERT INTO doctors SELECT * FROM for_fixing;

You may need to address one other option when enabling certain constraints. The unique constraint and the primary key constraint require an index to help enforce them. The index may not exist while a constraint is disabled, in which case it will need to be built when the constraint is enabled. You need to include the USING INDEX phrase as part of the ENABLE clause if you don't want the new index to use your default tablespace, or you want to override one or more of the default storage or space utilization parameters. Should the index already exist, the USING INDEX clause will be ignored. The syntax for the USING INDEX phrase is as follows:

USING INDEX
    [PCTFREE integer]
    [INITRANS integer]
    [MAXTRANS integer]
    [STORAGE (
        [INITIAL integer [K|M] ]
        [NEXT integer [K|M] ]
        [PCTINCREASE integer]
        [MINEXTENTS integer]
        [MAXEXTENTS integer]
        [FREELISTS integer]
        [FREELIST GROUPS integer] ) ]
    TABLESPACE tablespace_name
    NOSORT
    [NO[LOGGING]]

The terms used in this phrase are a subset of the options for the CREATE INDEX command, which you can find described in detail in Chapter 8 "Adding Segments for Different Types of Indexes."

SEE ALSO
A complete description of the options when creating an index,

Forcing Input with the NOT NULL Constraint

You use the NOT NULL constraint when your table contains a column that's required to hold a value for every single row in the table. If you're familiar with entity-relationship models, an attribute at the mandatory end of a relationship would always have to contain a value. In a business environment, it might be a requirement that every order in the ORDERS table contain a valid method of payment, such as a credit-card number, a bank account number for customers paying by check, or a flag to indicate that cash has been received.

A NOT NULL constraint can't be defined on multiple columns, but you can use a NOT NULL constraint on as many different columns in a single table as you need. To add a NOT NULL constraint, use the clause CONSTRAINT constraint_name NOT NULL. Remember that if you don't mind Oracle naming your constraint for you, you can omit the keyword CONSTRAINT and the constraint's name. You can also include any of the deferrable and validation options discussed earlier if you're assigning the constraint as part of a CREATE TABLE command, or if you're adding a new column to an existing table. However, you can't use these options when you're adding a NOT NULL constraint to an existing column.

Ensuring Distinct Values with Unique Constraints

You may need one or more columns in a table to contain no duplicate data. A table containing data being collected in real time may need each record to contain a distinct time stamp, for example, so that a user can tell the exact order in which the data was gathered. You may have to ensure that no two employees share the same userid in the company's email system when you add new records to the EMPLOYEES table. A unique constraint provides a simple method to enforce such a requirement.

A unique constraint can be defined on a single column or on a collection of columns. In the latter case, it's known as a composite unique constraint. If a single column is constrained with a unique key, you can still store multiple records with a NULL value in that column. With a composite unique constraint, two records are considered duplicates if they contain identical values in every column covered by the constraint. The same value can appear in the same column of many records without violating the constraint if at least one column in each record has a different value. NULLs in columns of a composite unique constraint are ignored when comparing two different records, so uniqueness has to be provided by the values in the non-NULL columns. Table 17.2 shows pairs of records from a two-column composite unique constraint and identifies which are considered duplicates.

As with other constraint types, you can add unique constraints by using your own name for them, or allow Oracle to name them for you. You also can make them deferrable or not, with the default behavior for deferrable constraints dependent on whether the user begins a transaction as deferrable. Similarly, the constraint can be created in an enabled or a disabled mode. (See the preceding sections for details of these characteristics.) The full syntax of the clause for creating unique constraints is as follows:

[CONSTRAINT constraint_name]
    UNIQUE [column_name[, column_name[...]]]
    [deferred_clause]
    [enabled clause]
    [exceptions_clause]
    [index_clause]

• CONSTRAINT constraint_name applies your name to the constraint. Omitting it will let the name be an Oracle-supplied name.
• UNIQUE is the required keyword for a unique constraint.
• deferred_clause contains any required DEFERRABLE and initial phrases and values. (See the detailed syntax in the earlier section "Statement or Transaction Enforcement.")
• enabled_clause contains either ENABLED or DISABLED.
• exceptions_clause identifies the table where invalid rows can be identified, as discussed in the "Enabling Constraints" section.
• index_clause has the following syntax:

USING INDEX
    [PCTFREE integer]
    [INITRANS integer]
    [MAXTRANS integer]
    [STORAGE (
        [INITIAL integer [K|M] ]
        [NEXT integer [K|M] ]
        [PCTINCREASE integer]
        [MINEXTENTS integer]
        [MAXEXTENTS integer]
        [FREELISTS integer]
        [FREELIST GROUPS integer] ) ]
    TABLESPACE tablespace_name
    NOSORT
    [[NO]LOGGING]

The terms in the USING INDEX clause are identical to those involved when building a new index with the CREATE INDEX command; see Chapter 8 where they're described in detail. Without a USING INDEX clause, the required index will be created in the same tablespace as the table if it's a new column and a new constraint; if it's a constraint being enabled it's built in the user's default tablespace. Of course, if there's already an index on the column(s) covered by the unique constraint, you shouldn't include the USING INDEX clause-otherwise the statement will fail.

Indexes created automatically through the addition of a unique constraint are also dropped automatically when the constraint is dropped or disabled. Oracle names these indexes with the same name as the constraint itself, whether it's an Oracle- or a user-supplied constraint name. If the index exists on the table before the constraint is created, it can't be dropped unless the constraint is dropped or disabled. You can't name your constraint with the same name as the index in such cases.

SEE ALSO
A complete description of the options when creating an index,

Creating Distinct Rows with Primary Key Constraints

A relational database table can't distinguish between two rows that have exactly the same values in all their columns. It's therefore advisable to create every table with a primary key constraint. This type of constraint guarantees that every row has some unique value that makes it distinguishable from every other row. Although there can be many columns in some tables that fulfill this requirement, only one primary key constraint is allowed per table.

A primary key constraint has the characteristics of the NOT NULL and the unique constraints. However, if the primary key is a composite constraint (a composite primary key constraint)-that is, defined across more than one column-the NOT NULL characteristic is applied to each individual column. Therefore, you can't store a row with a NULL value in a primary key column. This is different from composite unique constraints, which do allow NULLs in one or more columns.

Other than the constraint type keyword, the syntax for a primary key constraint is identical to that for a unique constraint. The syntax is shown here, but you're referred to the preceding section, which discusses unique constraints, for the details of the various clauses:

[CONSTRAINT constraint_name]
    PRIMARY KEY [column_name [,column_name[...]]]
    [deferred_clause]
    [enabled clause]
    [exceptions_clause]
    [index_clause]

An index is used just as for unique constraints to enforce a primary key's unique qualities. An index enforcing a primary key behaves in exactly the same way as one for a unique constraint, including what happens if it pre-exists or if it's created automatically; see the previous discussion on unique constraints for information about primary key indexes.

Validating Data Against Existing Records with Foreign Key Constraints

Earlier in this chapter, we discussed two tables that contained records with a dependency between them. In case you skipped that section, the tables were for a medical facility and one, DOCTORS, contained information about the physicians who practice there; the second was a PATIENTS table with information that included the ID number of the patient's doctor. To ensure that every patient is assigned to a doctor in the practice, the DOCTOR_ID column in the PATIENTS table needs to be validated against the ID column in the DOCTORS table. This can be achieved with a foreign key constraint, also known as a referential integrity constraint.

You create foreign key constraints by identifying the column (or columns if it's a composite foreign key) in the parent table that contains the data against which new records are to be checked. In the example you would put a foreign key constraint on the PATIENTS table's DOCTOR_ID column. This constraint would identify the DOCTORS table's ID column as the column containing the data against which patient records would have to be checked.

Unlike primary key or unique constraints, an index isn't required to support a foreign key constraint. However, without an index on the foreign key, some SQL statements performed against the parent table may cause locks to be placed on the table with the foreign key. These locks can prevent one or more users from accessing the table concurrently and might slow overall processing as a result. It's therefore recommended that you build an index on any foreign key constraints that you define. This will reduce the number of locks on the table and cause less restrictive locks to be placed on the index instead.

Before you try to add a foreign key constraint to a table, you need to confirm that the parent table has a primary key or a unique constraint defined on the columns you need your foreign key to reference. Oracle relies on this constraint to maintain a valid relationship between the two tables. If the constraint on the parent table is disabled or dropped, the foreign key also has to be disabled or dropped. A CASCADE option for these commands causes the appropriate action on the foreign key to occur. You also need to determine what happens to records in your table if the related record in the parent table is deleted. You have the option of preventing the DELETE from occurring if you have a child record still in place, or you can elect to allow the DELETE to delete any records with the same value in the foreign key column(s).

The syntax for a foreign key constraint is as follows:

[CONSTRAINT constraint_name]
    [FOREIGN KEY (column_name[, column_name[...]]) ]
    REFERENCES [schema_name.]table_name
[(column_name[, column_name[...]] ) ]
[ON DELETE CASCADE]
[deferred_clause]
    [enabled clause]
    [exceptions_clause]

• The CONSTRAINT clause optionally assigns the given name to the constraint.
• The FOREIGN KEY clause is required only if the constraint is being defined outside the scope of the column definition (a column constraint versus a table constraint, as discussed later in this chapter). When required, it also must name all the columns covered by the constraint.
• The keyword REFERENCES is required and identifies the parent table.
• The schema_name is required if the parent table isn't in the same schema as the table to which the foreign key constraint is being assigned.
• The table_name is the name of the parent table.
• The column_name, or series of column_names, are the columns in the parent table that hold the values being referenced; they're required only if they don't comprise the parent table's primary key.
• ON DELETE CASCADE is an optional clause that allows the deletion of records from the table if the referenced parent record is deleted.
• the deferred_clause, enabled clause, and exceptions_clause perform the identical actions as described in detail for unique constraints.

To explain the action of the ON DELETE CASCADE option, consider the example of the DOCTORS and PATIENTS tables. The PATIENTS table has a foreign key constraint on the DOCTOR_ID column, which references the ID column in DOCTORS. Suppose that a doctor has an ID of 22 in the DOCTORS table and that the PATIENTS table has a number of records for patients of doctor number 22. If this doctor decides to leave the practice, the record for this doctor should be deleted from the DOCTORS table. If this were allowed without any further action the patient records with DOCTOR_ID equal to 22 would violate the foreign key constraint.

By default, Oracle would prevent the deletion of the doctor record because of this resulting constraint violation. However, if the constraint were defined with the ON DELETE CASCADE option, the deletion of the record for doctor 22 from the DOCTORS table would also cause the records with DOCTOR_ID equal to 22 to be deleted from the PATIENTS table. These cascaded deletes occur automatically and without any feedback to the user that any patients are being deleted.

Although you may not need to use them very often, you can also define self-referencing constraints. These are foreign key constraints where the constrained column is in the same table as the referenced column, or (to put it another way) where the parent table and the child table are one and the same table. A parts and assemblies table may use such a constraint to ensure that every component listed for an assembly is itself a valid component or assembly as verified by another entry in the table.

Defining Business Rules with Check Constraints

Check constraints allow you to write your own conditions to determine whether the value in a column is valid. The conditions must evaluate to true or false, but they can include comparisons between two different columns in the same row as well as comparisons of column values to constants or functions. There are many uses for check constraints. Some that we've encountered include the following:

• Requiring that the value in a gender column be M or F
• Ensuring that birth date is at least 18 years less than hire date
• Checking that both or neither of two specific columns contain NULLs
• Avoiding storing a negative quantity-on-hand value

Although they're powerful, check constraints may not be able to define all your needs. For example, they can't compare values in two different rows in the same table, nor check values in a different table. The former restriction also means that you can't include an aggregate function, such as MIN or AVG, in a check constraint because they implicitly obtain data from multiple rows. In addition, check constraints can't include subqueries or functions that return values based on something outside the record being constrained.

These restrictions are all related to the issue that if the constraint were valid at one point in time, a change to some other record could invalidate it. For example, the AVG function is disallowed because any new record in a table could change the average value of a numeric column. Similarly, a subquery that retrieves records from the table could easily return different results after the new row for which the constraint is being checked is stored. Functions such as SYSDATE and USERID and pseudocolumns such as ROWNUM will be different depending on when the row is stored, who stores it, or where in the table it's located.

For complex rules, you can add as many check constraints to a column as you need to guarantee valid data. If a business rule becomes too complicated, you might not be able to use a check constraint or any other type of constraint to enforce it. You'll need to add a trigger to the table, or possibly handle the validation in the application code. Although not as efficient as a constraint, these techniques are required for certain types of integrity enforcement.

The check constraint syntax is quite simple compared with other constraints, and consists of the standard naming, deferred, and enabling options as well as the CHECK keyword and the related condition. The condition is placed inside parentheses, resulting in the following structure:

[CONSTRAINT constraint_name]
    CHECK (condition)
    [deferred_clause]
    [enabled clause]
    [exceptions_clause]

All components of this syntax, except the CHECK clause, were discussed in detail earlier in the section on unique constraints, so this information isn't repeated here. Instead, look at the condition clause by examining some examples. Table 17.3 shows the business rules listed at the beginning of this section and a check condition that could perform the required validation.

Including Constraints in a New Table Definition

When a new table is created, you can include any required constraints as part of the CREATE TABLE command. For single-column constraints, you can include the constraint definition as part of the column clause or add the definitions following all the column clauses. Constraints defined with their columns are called column constraints by Oracle, whereas constraints defined independently of their columns are called table constraints. This distinction is made only because the constraints' syntax varies slightly in some cases, depending on whether the constraint is defined at column or table level. Any constraint will work in exactly the same way, regardless of which type of definition was used to create it.

Some constraints can be defined only by using one or the other constraint type. Composite constraints, for example, must be defined as table constraints because they aren't part of any single column definition.

The general syntax for defining a table with constraints is as follows:

CREATE TABLE table_name (
    column_name column_datatype [(length)]
        [column_constraint [column_constraint [...]]],
    column_name column_datatype [(length)]
        [column_constraint [column_constraint [...]]],
    ...
    column_name column_datatype [(length)]
        [column_constraint [column_constraint [...]]],
    table_constraint [, table_constraint [...]] )

Chapter 7includes details of the column_name, column_type, and length definitions, as well as other clauses (not shown here) relating to the table's extents, space utilization, and similar characteristics. We want to examine only the constraint clauses here.

You can see from the syntax that a column definition can include one, many, or no constraint clauses. If there are constraint definitions, they all precede the comma that separates one column definition from the next. Table constraints follow the last column definition and are separated from it and from each other by commas. The other major difference in syntax between the two types of constraint definitions is that, because it's implied, a column constraint doesn't need to include the column name as part of its definition. Naturally, a table constraint must include the column name or names to which it applies because there's no syntactical link between the definition and any specific column definition.

There are some specific restrictions regarding constraint definitions when creating a table:

• A NOT NULL constraint must be defined as a column constraint.
• A composite constraint must be defined as a table constraint.
• Only a table-level foreign key constraint requires the FOREIGN KEY clause.
• Constraints enforced through the existence of an index (primary keys and unique constraints) can't violate the limits of an index-namely, they can't include more than 32 columns, and the total key length must be less than half the size of an Oracle block.

As an example of a table using different constraints, look over the following script file, which contains a CREATE TABLE command:

CREATE TABLE orders
  id              NUMBER(10)
                  CONSTRAINT orders_pk_id PRIMARY KEY,
  order_date      DATE CONSTRAINT orders_nn_date NOT NULL,
  customer_id     NUMBER(10)
                  CONSTRAINT orders_nn_customer NOT NULL
                  CONSTRAINT orders_fk_customer
                    REFERENCES CUSTOMERS (id),
  status          CHAR(5)
                  CONSTRAINT orders_ck_status CHECK (status
                    IN ('NEW','SHPD','PRTL','CANC','HOLD')),
  total_order     NUMBER(20,2),
  shipped_value   NUMBER(20,2),
  unshipped       NUMBER(20,2),
  payment_type    VARCHAR2(15),
  credit_rating   CHAR(4)
                  CONSTRAINT orders_ck_credit_rating CHECK
                    (credit_rating IN
                    ('EXCL','GOOD','FAIR','POOR',UNKN')),
  sales_rep       NUMBER(10),
  sales_region    NUMBER(10),
  CONSTRAINT orders_ck_ship_total
    CHECK (total_order=shipped_value+unshipped),
  CONSTRAINT orders_ck_payment_rating
    CHECK (payment_type IS NOT NULL
    OR credit_rating IS NOT NULL),
  CONSTRAINT orders_fk_sales_rep_region
    FOREIGN KEY (sales_rep, sales_region)
    REFERENCES employees (id, region) )
/

Adding a Constraint to an Existing Table

You need to use the ALTER TABLE command to add a constraint to a column in an existing table. You need to use the table constraint definition for all constraint types (except NOT NULL). The command is as follows:

ALTER TABLE table_name ADD (
    table_constraint [, table_constraint [...]] )

As you can see you can add more than one constraint with a single command; and, as with the CREATE TABLE command, each constraint clause can include any valid option for the constraint type. These options are described in detail in the earlier sections of this chapter.

If you need to make a column NOT NULL, you need to use the ALTER TABLE command with the MODIFY keyword. The syntax for this command is as follows:

ALTER TABLE table_name MODIFY (
    column_name column_constraint
    [, column_constraint [...]] )

column_constraint is a NOT NULL constraint with any optional clauses you want to use. Again, as with adding new constraints, you can use a single command to add multiple NOT NULL constraints to a table.

If you're adding one or more new columns to an existing table, you also use the ALTER TABLE command but now include the column definition. This definition may also include one or more constraints, or you can include table constraints in the statement following the new column definitions.

Modifying and Dropping Constraints

You might need to modify the constraint definitions you've created for a table. These modifications could include enabling a constraint, disabling or dropping a constraint, or changing a NOT NULL column to allow NULLs. All these changes require the use of the ALTER TABLE command.

The earlier section on enabling and disabling constraints looks at the details of the enable and disable clauses, so they're not repeated here. To perform these actions, you need to embed these clauses in the ALTER TABLE command:

ALTER TABLE  table_name enable_clause
ALTER TABLE table_name disable_clause

You can include multiple enable or disable clauses in a single ALTER TABLE command, and even use both types of clauses in a single statement. However, you can name only one constraint in each clause.

The following script contains the code necessary to redefine the index used for the primary key constraint defined on the ORDERS table's ID column. It shows the use of both the DISABLE and ENABLE options of the ALTER TABLE command.

	To drop the index associated with the primary key constraint, you need to disable (or drop) the constraint
	Use the keywords to disable the constraint; because there's only one primary key per table
	The index will be rebuilt when you enable the primary key (again using the key-words)
	Include this clause to over-ride default index creation
	Use this clause to override index creation in your default tablespace
	Include other space utilization parameters if needed
	You don't need to leave free space if the index is on a column with an ever-increasing value
	Use whatever options are appropriate for your index
	You may want to include this in case an invalid record was added while the constraint was disabled
	You need to name your table if you don't use the default created by ULTEXCPT.SQL

Dropping a constraint is similar to disabling one. You have the same options to identify the constraint: by name or by the keywords PRIMARY KEY and UNIQUE, the latter with the list-included columns. The CASCADE option is also available should you wantto force any foreign key constraints that depend on the constraint being dropped concurrently. As with the disable action, you can't drop a constraint if there's a dependent constraint. The syntax for dropping a constraint follows. (Refer to the section on disabling constraints if you need clarification on any of the included terms; they perform identical duties in either command.)

ALTER TABLE table_name DROP
    [PRIMARY KEY]
    [UNIQUE (column_name[, column_name [...] ] ) ]
    [CONSTRAINT constraint_name]

As with the enable and disable clauses, you can include only one constraint in an ALTER TABLE...DROP command.

Again, the NOT NULL constraint doesn't completely conform to the syntactical rules of the other constraint types. Although you can use all the preceding commands to enable, disable, or drop a NOT NULL constraint, you can also use an ALTER TABLE...MODIFY statement to switch a column between allowing and disallowing NULLs. The statement has the following form:

ALTER TABLE table_name MODIFY
(column_name [CONSTRAINT not_null_constraint] [NOT] NULL
[,column_name [CONSTRAINT not_null_constraint] [NOT] NULL]
[,...] )

As you can see, this command allows you to alter the NULL enforcement for a single column or multiple columns in a single command. You can even include column changes from NULL to NOT NULL and from NOT NULL to NULL within the same statement, although you can't change the same column twice within one command. The command also gives you the option of using the constraint name (if there is one), but works just as well if you don't include it.

One final issue you need to consider when using constraints: Your command will fail if you try to drop a table that's being referenced by at least one foreign key constraint in a different table. The exception message will indicate that the primary key or a unique key in the table is required by the foreign key reference. To drop the table, therefore, you must drop the foreign key constraint. You can do this by manually dropping the foreign key constraint before you attempt to drop the referenced table. You can also elect to include the keyword CASCADE CONSTRAINTS clause in your DROP TABLE command in order to remove all dependent constraints. However, because it does this automatically without letting you know how many or which constraints are dropped, it isn't necessarily a wise option to use. It may behoove you to check why any dependent constraints exist before dropping the parent table.

Using Default Column Values

Oracle provides a mechanism-although it's not strictly a constraint-to supply a default value for a column when the initial INSERT statement fails to provide a value. The default can be a constant or an expression, provided it doesn't include a reference to another column or to CURRVAL, NEXTVAL, LEVEL, or ROWNUM. A common use of default values is to store the current date and time obtained through the SYSDATE function whenever a record is added to a table. The following column definition shows this type of default:

time_stamp    DATE DEFAULT sysdate

A default column value can be changed at any time with an UPDATE command, so you would also need to add a constraint to the column if you want to restrict its allowable values when a record is created.

---

© Copyright, Macmillan Computer Publishing. All rights reserved.