2. Oracle Specifics

• You can define integrity constraints to enforce business rules on data in your tables.
• Once an integrity constraint is enabled, all data in the table must conform to the rule that it specifies.
• If you subsequently issue a SQL statement that modifies data in the table, Oracle ensures that the resulting data satisfies the integrity constraint.
• Without integrity constraints, such business rules must be enforced programmatically by your application.

• Enforcing rules with integrity constraints is less costly than enforcing the equivalent rules by issuing SQL statements in your application.
• The semantics of integrity constraints are very clearly defined, so the internal operations that Oracle performs to enforce them are optimized beneath the level of SQL statements in Oracle.
• Since your applications use SQL, they cannot achieve this level of optimization.
• Enforcing business rules with SQL statements can be even more costly in a networked environment because the SQL statements must be transmitted over a network. In such cases, using integrity constraints eliminates the performance overhead incurred by this transmission.

• For best performance, define and enable integrity constraints and develop your applications to rely on them, rather than on SQL statements in your applications, to enforce business rules.
• However, in some cases, you might want to enforce business rules through your application as well as through integrity constraints.
• Enforcing a business rule in your application might provide faster feedback to the user than an integrity constraint. For example, if your application accepts 20 values from the user and then issues an INSERT statement containing these values, you might want your user to be notified immediately after entering a value that violates a business rule.
• Since integrity constraints are enforced only when a SQL statement is issued, an integrity constraint can only notify the user of a bad value after the user has entered all 20 values and the application has issued the INSERT statement. However, you can design your application to verify the integrity of each value as it is entered and notify the user immediately in the event of a bad value.

By default, all columns can contain nulls. Only define NOT NULL constraints for columns of a table that absolutely require values at all times.

Choose unique keys carefully. In many situations, unique keys are incorrectly comprised of columns that should be part of the table's primary key (see the previous section for more information about primary keys). When deciding whether to use a UNIQUE key constraint, use the rule that a UNIQUE key constraint is only required to prevent the duplication of the key values within the rows of the table. The data in a unique key is such that it cannot be duplicated in the table.

Do not confuse the concept of a unique key with that of a primary key. Primary keys are used to identify each row of the table uniquely. Therefore, unique keys should not have the purpose of identifying rows in the table.

Some examples of good unique keys include

• an employee's social security number (the primary key is the employee number)
• a truck's license plate number (the primary key is the truck number)
• a customer's phone number, consisting of the two columns AREA and PHONE (the primary key is the customer number)
• a department's name and location (the primary key is the department number)

Whenever two tables are related by a common column (or set of columns), define a PRIMARY or UNIQUE key constraint on the column in the parent table, and define a FOREIGN KEY constraint on the column in the child table, to maintain the relationship between the two tables. Depending on this relationship, you may want to define additional integrity constraints including the foreign key, as listed in the following.

Several relationships between parent and child tables can be determined by the other types of integrity constraints defined on the foreign key in the child table.

• When no other constraints are defined on the foreign key, any number of rows in the child table can reference the same parent key value.
• This model allows nulls in the foreign key.
• This model establishes a "one-to-many" relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key.

An example of such a relationship is shown in the above picture between EMP and DEPT; each department (parent key) has many employees (foreign key), and some employees might not be in a department (nulls in the foreign key).

When nulls are not allowed in a foreign key, each row in the child table must explicitly reference a value in the parent key because nulls are not allowed in the foreign key. In this case, employees must have a reference to a specific department.

• When a UNIQUE constraint is defined on the foreign key, one row in the child table can reference a parent key value. This model allows nulls in the foreign key.
• This model establishes a "one-to-one" relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key.

For example, assume that the EMP table had a column named MEMBERNO, referring to an employee's membership number in the company's insurance plan. Also, a table named INSURANCE has a primary key named MEMBERNO, and other columns of the table keep respective information relating to an employee's insurance policy. The MEMBERNO in the EMP table should be both a foreign key and a unique key:

• to enforce referential integrity rules between the EMP and INSURANCE tables (the FOREIGN KEY constraint)
• to guarantee that each employee has a unique membership number (the UNIQUE key constraint)

When both UNIQUE and NOT NULL constraints are defined on the foreign key, only one row in the child table can reference a parent key value. Because nulls are not allowed in the foreign key, each row in the child table must explicitly reference a value in the parent key.

This model establishes a "one-to-one" relationship between the parent and foreign keys that does not allow undetermined values (nulls) in the foreign key.

If you expand the previous example by adding a NOT NULL constraint on the MEMBERNO column of the EMP table, in addition to guaranteeing that each employee has a unique membership number, you also ensure that no undetermined values (nulls) are allowed in the MEMBERNO column of the EMP table.

Oracle allows a column to be referenced by multiple FOREIGN KEY constraints; effectively, there is no limit on the number of dependent keys. This situation might be present if a single column is part of two different composite foreign keys.

• Use CHECK constraints when you need to enforce integrity rules that can be evaluated based on logical expressions.
• Never use CHECK constraints when any of the other types of integrity constraints can provide the necessary checking.

Examples of appropriate CHECK constraints include the following:

• a CHECK constraint on the SAL column of the EMP table so that no salary value is greater than 10000
• a CHECK constraint on the LOC column of the DEPT table so that only the locations "BOSTON", "NEW YORK", and "DALLAS" are allowed
• a CHECK constraint on the SAL and COMM columns to compare the SAL and COMM values of a row and prevent the COMM value from being greater than the SAL value

• A CHECK integrity constraint requires that a condition be true or unknown for every row of the table.
• If a statement causes the condition to evaluate to false, the statement is rolled back.
• The condition of a CHECK constraint has the following limitations:

• The condition must be a Boolean expression that can be evaluated using the values in the row being inserted or updated.
• The condition cannot contain subqueries or sequences.
• The condition cannot include the SYSDATE, UID, USER, or USERENV SQL functions.
• The condition cannot contain the pseudocolumns LEVEL, PRIOR, or ROWNUM;
• The condition cannot contain a user-defined SQL function.

When using CHECK constraints, consider the ANSI/ISO standard, which states that a CHECK constraint is violated only if the condition evaluates to false; true and unknown values do not violate a check condition. Therefore, make sure that a CHECK constraint that you define actually enforces the rule you need enforced.

For example, consider the following CHECK constraint:

	CHECK (sal > 0 OR comm >= 0)

At first glance, this rule may be interpreted as "do not allow a row in the EMP table unless the employee's salary is greater than zero or the employee's commission is greater than or equal to zero." However, note that if a row is inserted with a null salary and a negative commission, the row does not violate the CHECK constraint because the entire check condition is evaluated as unknown. In this particular case, you can account for such violations by placing NOT NULL integrity constraints on both the SAL and COMM columns.

• A condition that evaluates to UNKNOWN acts almost like FALSE.
• For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows.
• However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN.
• Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

The following table shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.

NOT Operator able 3-6 NOT Truth Table

AND Operator Table 3-7 AND Truth Table

OR Operator Table 3-8 OR Truth Table

A single column can have multiple CHECK constraints that reference the column in its definition. There is no limit to the number of CHECK constraints that can be defined that reference a column.

• According to the ANSI/ISO standard, a NOT NULL integrity constraint is an example of a CHECK integrity constraint, where the condition is

	CHECK (column_name IS NOT NULL)

• Therefore, NOT NULL integrity constraints for a single column can, in practice, be written in two forms: using the NOT NULL constraint or a CHECK constraint. For ease of use, you should always choose to define NOT NULL integrity constraints instead of CHECK constraints with the "IS NOT NULL" condition.
• In the case where a composite key can allow only all nulls or all values, you must use a CHECK integrity constraint.

		CHECK ((c1 IS NULL AND c2 IS NULL) OR 
			(c1 IS NOT NULL AND c2 IS NOT NULL))

Define an integrity constraint using the constraint clause of the SQL commands CREATE TABLE or ALTER TABLE.

The following examples of CREATE TABLE statements show the definition of several integrity constraints:

CREATE TABLE dept (
	deptno  NUMBER(3) PRIMARY KEY, 
	dname   VARCHAR2(15), 
	loc     VARCHAR2(15), 
        CONSTRAINT dname_ukey UNIQUE (dname, loc), 
        CONSTRAINT loc_check1 
                CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')));
CREATE TABLE emp ( 
	empno    NUMBER(5) PRIMARY KEY, 
	ename    VARCHAR2(15) NOT NULL, 
	job      VARCHAR2(10), 
	mgr      NUMBER(5) CONSTRAINT mgr_fkey 
                 REFERENCES emp,
	hiredate DATE, 
	sal      NUMBER(7,2), 
	comm     NUMBER(5,2), 
	deptno   NUMBER(3) NOT NULL 
		   CONSTRAINT dept_fkey 
		       REFERENCES dept ON DELETE CASCADE);

You can also define integrity constraints using the constraint clause of the ALTER TABLE command. For example, the following examples of ALTER TABLE statements show the definition of several integrity constraints:

ALTER TABLE dept 
	ADD PRIMARY KEY (deptno); 
ALTER TABLE emp 
	ADD CONSTRAINT dept_fkey FOREIGN KEY (deptno) REFERENCES dept 
	MODIFY (ename VARCHAR2(15) NOT NULL);

Because data is likely to be in the table at the time an ALTER TABLE statement is issued, there are several restrictions to be aware of. Table 9-1 lists each type of constraint and the associated restrictions with the ALTER TABLE command.

Assumes DISABLE clause not included in statement.

If you attempt to define a constraint with an ALTER TABLE statement and violate one of these restrictions, the statement is rolled back and an informative error is returned explaining the violation.

The creator of a constraint must have the ability to create tables (that is, the CREATE TABLE or CREATE ANY TABLE system privilege) or the ability to alter the table (that is, the ALTER object privilege for the table or the ALTER ANY TABLE system privilege) with the constraint. Additionally, UNIQUE key and PRIMARY KEY integrity constraints require that the owner of the table have either a quota for the tablespace that contains the associated index or the UNLIMITED TABLESPACE system privilege. FOREIGN KEY integrity constraints also require some additional privileges.

• Assign names to NOT NULL, UNIQUE KEY, PRIMARY KEY, FOREIGN KEY, and CHECK constraints using the CONSTRAINT option of the constraint clause.

• This name must be unique with respect to other constraints that you own. If you do not specify a constraint name, one is assigned by Oracle.

• See the previous examples of the CREATE TABLE and ALTER TABLE statements for examples of the CONSTRAINT option of the Constraint clause. Note that the name of each constraint is included with other information about the constraint in the data dictionary.

By default, whenever an integrity constraint is defined in a CREATE or ALTER TABLE statement, the constraint is automatically enabled (enforced) by Oracle unless it is specifically created in a disabled state using the DISABLE clause.

When defining UNIQUE key, PRIMARY KEY, and FOREIGN KEY integrity constraints, you should be aware of several important issues and prerequisites. For information about defining and managing FOREIGN KEY constraints

This section explains the mechanisms and procedures for manually enabling and disabling integrity constraints.

In summary, an integrity constraint can be thought of as a statement about the data in a database. This statement is always true when the constraint is enabled; however, the statement may or may not be true when the constraint is disabled because data in violation of the integrity constraint can be in the database.

To enforce the rules defined by integrity constraints, the constraints should always be enabled; however, in certain situations, it is desirable to disable the integrity constraints of a table temporarily for performance reasons. For example:

• when loading large amounts of data into a table using SQL*Loader
• when performing batch operations that make massive changes to a table (such as changing everyone's employee number by adding 1000 to the existing number)
• when importing or exporting one table at a time

In cases such as these, integrity constraints may be temporarily turned off to improve the performance of the operation.

If a row of a table does not adhere to an integrity constraint, this row is said to be in violation of the constraint and is known as an exception to the constraint. If any exceptions exist, the constraint cannot be enabled. The rows that violate the constraint must be either updated or deleted in order for the constraint to be enabled.

Exceptions for a specific integrity constraint can be identified while attempting to enable the constraint. This procedure is discussed in the section "Exception Reporting" on page 9-23.

When you define an integrity constraint in a CREATE TABLE or ALTER TABLE statement, you can enable the constraint by including the ENABLE clause in its definition or disable it by including the DISABLE clause in its definition. If neither the ENABLE nor the DISABLE clause is included in a constraint's definition, Oracle automatically enables the constraint.

The following CREATE TABLE and ALTER TABLE statements both define and enable integrity constraints:

CREATE TABLE emp (
    empno NUMBER(5) PRIMARY KEY,   . . . );
 ALTER TABLE emp 
    ADD PRIMARY KEY (empno);

An ALTER TABLE statement that defines and attempts to enable an integrity constraint may fail because rows of the table may violate the integrity constraint. In this case, the statement is rolled back and the constraint definition is not stored and not enabled.

The following CREATE TABLE and ALTER TABLE statements both define and disable integrity constraints:

CREATE TABLE emp (  
	empno NUMBER(5) PRIMARY KEY DISABLE,   . . . ); 
ALTER TABLE emp 
	ADD PRIMARY KEY (empno) DISABLE;

An ALTER TABLE statement that defines and disables an integrity constraints never fails. The definition of the constraint is always allowed because its rule is not enforced.

Use the ALTER TABLE command to

• enable a disabled constraint, using the ENABLE clause
• disable an enabled constraint, using the DISABLE clause

The following statements are examples of statements that enable disabled integrity constraints:

ALTER TABLE dept  
	ENABLE CONSTRAINT dname_ukey; 
 
ALTER TABLE dept 
	ENABLE PRIMARY KEY,  
	ENABLE UNIQUE (dname, loc); 

An ALTER TABLE statement that attempts to enable an integrity constraint fails when the rows of the table violate the integrity constraint. In this case, the statement is rolled back and the constraint is not enabled.

The following statements are examples of statements that disable enabled integrity constraints:

ALTER TABLE dept  
	DISABLE CONSTRAINT dname_ukey;
 
ALTER TABLE dept 
	DISABLE PRIMARY KEY,  
	DISABLE UNIQUE (dname, loc);

When enabling or disabling UNIQUE key, PRIMARY KEY, and FOREIGN KEY integrity constraints, you should be aware of several important issues and prerequisites. For more information about enabling, disabling, and managing FOREIGN KEY constraints. UNIQUE key and PRIMARY KEY constraints are usually managed by the database administrator.