Referential integrity refers to the methods used to manage the relationships between tables. When you create a table, you can define constraints to ensure that the data inserted into a particular column has matching values in another table.
There are three types of references you can define in a table: references to another table, references from another table, and self-references, that is, references within the same table. The referential integrity constraints in these examples are defined at the column level, using the references keyword in the create table statement.
The following two tables from the pubs3 database illustrate how declarative referential integrity works. The first table, stores, is a “referenced” table:
create table stores (stor_id char(4) not null, stor_name varchar(40) null, stor_address varchar(40) null, city varchar(20) null, state char(2) null, country varchar(12) null, postalcode char(10) null, payterms varchar(12) null, unique nonclustered (stor_id))
The second table, store_employees, is a “referencing table” because it contains a reference to the stores table. It also contains a self-reference:
create table store_employees (stor_id char(4) null references stores(stor_id), emp_id id not null, mgr_id id null references store_employees(emp_id), emp_lname varchar(40) not null, emp_fname varchar(20) not null, phone char(12) null, address varchar(40) null, city varchar(20) null, state char(2) null, country varchar(12) null, postalcode varchar(10) null, unique nonclustered (emp_id))
The references defined in the store_employees table enforce these restrictions:
Any store specified in the store_employees table must be included in the stores table. The references constraint enforces this by verifying that any value inserted into the stor_id column in store_employees must already exist in the stor_id column in my_stores.
All managers must have employee identification numbers. The references constraint enforces this by verifying that any value inserted into the mgr_id column must already exist in the emp_id column.
Overview
Enforcing Constraints
Maintaining Constraints
Overview
Data integrity guarantees that data adheres to business rules. There are three primary ways achiveing this:
- Declarative integrity constraints. Integrity constraints include the following types:
- NOT NULL. Specify a column cannot contain null values (no value is assigned).
- UNIQUE. Indicate the values of a column (or columns) must be unique.
- PRIMARY KEY. Designate a column (or columns) as the table's primary key.
- FOREIGN KEY. Designate a column (or columns) as the foreign key in a referential integrity constraint.
- CHECK. Specify a condition that each row of the table must satisfy.
- Application Codes. Application codes may be implemented either as stored procedures within database (to be covered in the Chapter on PL/SQL) or as applications running on the client.
- Database triggers. Database triggers are PL/SQL programs that are executed when a certain event such as an insert or update of a column occurs on a table. Triggers are usually created only to enforce a complex business rule that cannot be defined by an integrity constraints. Triggers can be enabled or disabled.
In this section, we focus on the integrity constraints.
Creating Constraints
An integrity constraint can be enabled or disabled, validated or non-validated over existing data (for the purpose of constraint violation checking) which are created before the enabling of constraint.
Primary key and unique key constraints are enforced using indexes (creating index is covered in the chapter on managing indexes). If a constraint is enabled, oracle will create a unique index on the constraint column if the index does not unique, or reuse the existing index.
Foreign key constraint prevents the incorrect manipulation of data references. For example, the DEPARTMENT_ID column in an EMPLOYEE table refers to a parent table DEPARTMENT. A foreign key constraint on DEPARTMENT_ID referring to DEPARTMENT table can prevent us from 1) inserting a new record into EMPLOYEE with a non-existing DEPARTMENT_ID; 2) deleting a record in DEPARTMENT table whose DEPARTMENT_ID value is used in EMPLOYEE table.
Constraints can be created in-line or out-of-line.
- In-line constraints are constraints defined in creating a table.
For example:
CREATE TABLE employee (
id NUMBER(7) CONSTRAINT employee_id_pk PRIMARY KEY,
name VARCHAR2(50) CONSTRAINT name_nn NOT NULL,
dept_id NUMBER(7));
- Out-of-line constraint are defined with ALTER TABLE command.
For example:
ALTER TABLE employee
ADD (CONSTRAINT employee_dept_id_fk FOREIGN KEY (dept_id)
REFERENCES department (id));
Maintaining Constraints
A constraint that is currently disabled can be disabled in one of the two ways: enable NOVALIDATE or enable VALIDATE. The former does not check existing data for constraint violation.
The simplified syntax is:
ALTER TABLE <table name>
ENABLE NOVALIDATE|VALIDATE {CONSTRAINT <constraint name> | PRIMARY KEY | UNIQUE (column)}
For example, assuming the primary key is not defined for table EMPLOYEE.
SQL> ALTER TABLE employee ENABLE VALIDATE emp_dept_id_fk;
SQL> ALTER TABLE employee ENABLE VALIDATE PRIMARY KEY (id);
OR
SQL> ALTER TABLE employee ENABLE NOVALIDATE emp_dept_id_fk;