Create and Manage Constraints

Constraints enforce business rules in the database. In other words, they limit the acceptable data values for a table. Constraints are optional schema objects that depend on tables. Although you can have a table without any constraints, you cannot create a constraint without a table. Oracle lets you create several types of constraints on your tables to enforce your business rules, including the following:

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. REFERENTIAL
5. CHECK
   

You can create constraints together with the table in the CREATE TABLE statement. After you create a table, you add or remove a constraint from a table with an ALTER TABLE statement. You specify the constraint information with either the in-line syntax as a column attribute or the out-of-line syntax as part of the table definition. Constraints do not require a name; if you do not name the constraint, Oracle generates one for you. However, the generated names are simply numbers prefixed with SYS_C and may not be very meaningful. The following sections describe each of the constraint types in detail.


NOT NULL Constraints

By default, all columns in a table allow NULL as a valid value. A NULL represents unknown or nonexistent information. Some business rules can be enforced with a NOT NULL constraint. For example, an employee may not be considered a valid employee if their hire date is not known. You enforce this business rule by placing a NOT NULL constraint on the hire_date column of
the employees table. Any INSERT or UPDATE statements fail if the protected column does not have a value. NOT NULL constraints must be declared together with the column definition using in-line syntax. Here is an example using the NOT NULL constraint:

CREATE TABLE employees
(employee_id NUMBER CONSTRAINT nn_emp_id NOT NULL
,hire_date DATE NOT NULL ,first_name VARCHAR2(42)
,last_name VARCHAR2(42) );


UNIQUE Constraints

A UNIQUE constraint ensures that each occurrence of the columns protected by this constraint is different from all other occurrences in the table. UNIQUE constraints cannot be created on columns of type CLOB, NCLOB, BLOB, LONG, LONG RAW, or TIMESTAMP WITH TIMEZONE. Here is how you create an employees table that has a UNIQUE constraint on the payroll_id column using the out-of-line syntax:

CREATE TABLE employees
(employee_id NUMBER NOT NULL,hire_date DATE NOT NULL
,first_name VARCHAR2(42),last_name VARCHAR2(42)
,payroll_id VARCHAR2(10),CONSTRAINT uniq_payroll_id UNIQUE (payroll_id)
);

Using the in-line syntax, the statement looks like this:

CREATE TABLE employees
(employee_id NUMBER NOT NULL
CONSTRAINT uniq_payroll_id UNIQUE
,hire_date DATE NOT NULL ,first_name VARCHAR2(42)
,last_name VARCHAR2(42) ,payroll_id VARCHAR2(10)
);

No two rows in this table can have the same value in payroll_id. NULL values do not count as a distinct value, so this employees table can have multiple rows with a NULL payroll_id. To ensure that payroll_id is always present, you need a NOT NULL constraint. The database uses an index to help enforce this constraint. The index is usually a unique index, and if you create the UNIQUE constraint together with the table, the database automatically creates a unique index on the columns protected by the UNIQUE constraint, and the name of the index defaults to the name of the constraint. To assign attributes to this index, take advantage of the USING INDEX clause, like this:

CREATE TABLE employees
(employee_id NUMBER NOT NULL ,hire_date DATE NOT NULL
,first_name VARCHAR2(42) ,last_name VARCHAR2(42)
,payroll_id VARCHAR2(10) ,CONSTRAINT uniq_payroll_id UNIQUE (payroll_id)
USING INDEX TABLESPACE indx );

You can add a UNIQUE constraint after the table is built by using an ALTER TABLE statement, like this:

ALTER TABLE employees ADD CONSTRAINT uniq_payroll_id UNIQUE (payroll_id) USING INDEX TABLESPACE indx ;

FOREIGN KEY Constraints

FOREIGN KEY constraints are also known as referential integrity constraints because they enforce referential integrity. FOREIGN KEY constraints enforce referential integrity by ensuring that data values referenced in one table are defined in another table. FOREIGN KEY constraints tie these two tables together in a parent/child or referenced/dependent relationship. Here is an example of creating a parent table (DEPARTMENTS) and child table (EMPLOYEES) with a PRIMARY KEY constraint on the parent and a FOREIGN KEY constraint on the child table, using out-of-line syntax:

CREATE TABLE departments
(dept_nbr NUMBER NOT NULL CONSTRAINT department_pk PRIMARY KEY ,dept_name VARCHAR2(32) ,manager_id NUMBER );

CREATE TABLE employees
(employee_id NUMBER NOT NULL ,hire_date DATE NOT NULL
,first_name VARCHAR2(42) ,last_name VARCHAR2(42)
,payroll_id VARCHAR2(10) ,dept_nbr NUMBER
,CONSTRAINT uniq_payroll_id UNIQUE (payroll_id)
USING INDEX TABLESPACE indx
,CONSTRAINT employees_pk PRIMARY KEY (employee_id)
USING INDEX TABLESPACE indx
,CONSTRAINT emp_dept_fk FOREIGN KEY (dept_nbr)
REFERENCES departments(dept_nbr) );


In this example, each employee belongs to a department, so we put a DEPT_NBR column in the EMPLOYEES table, which will hold each employee’s department number. The DEPARTMENTS table defines all the valid department numbers to ensure that DEPT_NBR values appearing in the EMPLOYEES table are defined in the DEPARTMENTS table—in essence that an employee belongs to
a valid department. You implement this relationship or rule with a FOREIGN KEY constraint. By default, FOREIGN KEYs allow NULLs.
By default, the database raises an exception and does not allow you to delete rows from a parent table if those rows are referenced in the child table. If this behavior isn’t what you want, you can tell the database to automatically maintain referential integrity in a couple of ways: bydeleting the child rows and specifying ON DELETE CASCADE or by setting the columns in the child
table to NULL with the ON DELETE SET NULL clause, like this:
ALTER TABLE employees ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dept_nbr) REFERENCES departments(dept_nbr) ON DELETE CASCADE;
ALTER TABLE departments ADD CONSTRAINT dept_mgr_fk FOREIGN KEY (manager_id) REFERENCES employees(employee_id) ON DELETE SET NULL;
The first statement tells the database that deleting a department should cause a cascading deletion of that department’s employees. The second statement tells the database that deleting an employee who is a department manager should cause that department’s MANAGER_ID column to revert to NULL.


A Self-Referencing Foreign Key

The parent and child tables do not always have to be separate tables; they can be separate columns of the same table. This configuration is known as a self-referencing foreign key. An example of a self-referencing foreign key can be added to the EMPLOYEES table used in the previous section. The business rule that will be enforced requires that each employee report to a manager and also that the manager be a valid employee. To add this rule to the EMPLOYEES
table, add the MANAGER column together with a FOREIGN KEY constraint on which it references the EMPLOYEES table, like this:

ALTER TABLE employees ADD
(manager NUMBER
,CONSTRAINT mgr_emp_fk FOREIGN KEY (manager)
REFERENCES employees(employee_id)
ON DELETE SET NULL
);


CHECK Constraints

CHECK constraints verify that a column or set of column values meet a specific condition that must evaluate to a Boolean. If the condition evaluates to FALSE, the database raises an exception, and the INSERT or UPDATE statement fails. The condition cannot include subqueries, references to other tables, or calls to functions that are not deterministic. A function is deterministic if it always returns the same result when passed the same input parameters. Examples of deterministic functions include SQRT, TO_DATE, and SUBSTR. The functions SYSDATE, USER, and DBTIMEZONE are not deterministic. The condition must be a Boolean SQL expression enclosed in parentheses. Add a CHECK constraint to ensure that every employee’s hire date is later than the company’s founding date, like this:

ALTER TABLE employees ADD CONSTRAINT validate_hire_date CHECK
(hire_date > TO_DATE('15-Apr-1999','DD-Mon-YYYY'));


Modifying Constraints

Once created, constraints can be dropped, disabled (temporarily not enforced), enabled (enforced again), or renamed. You make these changes to constraints using an ALTER TABLE statement. Take care in disabling UNIQUE or PRIMARY KEY constraints because disabling these constraints results in the supporting index being dropped (unless you also specify KEEP INDEX. To drop a constraint, use an ALTER TABLE statement with the constraint name, like this:

ALTER TABLE employees DROP CONSTRAINT validate_hire_date;