What type of constraints do you need to define to use declarative referential integrity ?

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:

1. 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.
2. 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.
3. 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;

What type of constraints do you need to define to ensure referential integrity?

What are the 3 types of integrity constraints?

What is declarative referential integrity?

Is referential integrity An integrity constraint?