Lesson 8	Implementing constraints
Objective	Identify how constraints are implemented.

Implementing Table Constraints in Oracle

In Oracle databases, the distinction between table constraints and column constraints is foundational to enforcing data integrity and establishing the rules that govern the permissible values within a database table. Understanding these constraints is essential for a database administrator (DBA) in order to ensure the database operates as intended.

Column Constraints:

Column constraints are declarative rules applied to individual columns within an Oracle table. They define the permissible values for that specific column. Here are the types of column constraints in Oracle:

NOT NULL: Ensures that a column cannot have a NULL value.
UNIQUE: Ensures all values in a column are unique across the table.
PRIMARY KEY: A combination of NOT NULL and UNIQUE. Ensures that the column has unique, non-null values that can uniquely identify each row in a table.
CHECK: Evaluates a condition (a Boolean expression) and ensures that all values in the column satisfy this condition.

Column constraints are defined at the time of column creation within the CREATE TABLE statement or added later with the ALTER TABLE statement, directly alongside the column definition.

For example:

CREATE TABLE employees (
    employee_id NUMBER(10) PRIMARY KEY,
    email VARCHAR2(100) UNIQUE NOT NULL,
    salary NUMBER(10, 2) CHECK (salary > 0)
);

Table Constraints:

Table constraints apply to the entire table rather than an individual column and can encompass multiple columns. Table constraints are used to define rules that span across several columns. Types of table constraints include:

PRIMARY KEY: Similar to the column constraint but can include multiple columns. Defines a set of columns where all combined values must be unique and not null across the table.
UNIQUE: Ensures that the combination of values in specified columns is unique across all rows in the table.
FOREIGN KEY: Establishes a referential integrity constraint between a key in the table (foreign key) and a primary or unique key in another table.
CHECK: Defines a condition for the entire table rather than a single column.

Table constraints are typically defined at the end of the CREATE TABLE statement or added with the ALTER TABLE statement.
Here is an example:

CREATE TABLE department (
    department_id NUMBER(10) NOT NULL,
    department_name VARCHAR2(50) NOT NULL,
    manager_id NUMBER(10),
    location_id NUMBER(10),
    CONSTRAINT pk_department PRIMARY KEY (department_id),
    CONSTRAINT fk_department_manager FOREIGN KEY (manager_id)
        REFERENCES employees (employee_id),
    CONSTRAINT chk_department_id CHECK (department_id > 0)
);

Key Differences:

Scope: Column constraints are defined for a single column, while table constraints can span multiple columns.
Context: Column constraints are usually defined within the column definition. Table constraints are defined separately and can reference multiple columns.
Flexibility: Table constraints provide more flexibility, as they can encompass complex conditions involving multiple columns, which is not possible with column constraints.

A clear grasp of these constraints and their proper application is vital for maintaining data integrity and the relational logic of an Oracle database. An adept DBA utilizes these constraints to ensure that data adheres to business logic and the data model's requirements.

Table constraints vs. Column Constraints

So far, we have used CONSTRAINT syntax that is associated with a particular table. Because most constraints refer to a particular column in a table, you may prefer to assign constraints as part of a column definition instead of the table definition. Below is an example of an SQL statement with a table constraint.

CREATE TABLE LOT (auction_id NUMBER, 
lot_id NUMBER, 
min_price NUMBER(11,2), 
auction_id NUMBER, 
winning_bid NUMBER(11,2), W
winning_bidder NUMBER,
CONSTRAINT fk_auction_id FOREIGN KEY (auction_id) REFERENCES auction(auction_id));

The SQL statement is equivalent to this definition, with the same constraint on the column:

CREATE TABLE LOT (auction_id NUMBER 
CONSTRAINT fk_auction_id REFERENCES auction(auction_id), 
lot_id NUMBER, min_prince NUMBER(11,2), 
auction_id NUMBER, 
winning_bid NUMBER(11,2), 
winning_bidder NUMBER;

You do not have to explicitly identify the column in a column-level constraint. You must define a table level constraint if the constraint includes more than one column, such as a primary key that is based on two or more columns.

Deferred Constraints

When you apply constraints, you have a choice of when you want them executed. You may impose the conditions when the SQL statement that affects how the constrained table is executed. Another choice is to defer the constraint's operation until the end of the transaction that includes the SQL statement that affects the constrained table. For instance, you may have a FOREIGN KEY constraint on a column, but you may not create the matching value for the primary key it refers to until a later SQL statement. If you defer the implementation of the FOREIGN KEY constraint, you will not get an error, because the required value will exist by the end of the transaction.
You can defer a constraint with the DEFERRABLE keyword as part of the constraint. You can use the

ALTER TABLE SET CONSTRAINTS DEFERRED

to defer the eligible constraints on a table and use the ALTER TABLE SET CONSTRAINTS IMMEDIATE to force all deferred constraints back to immediate operation. When you use the DEFFERABLE option, you can specify the keywords INITIALLY DEFFERRED or INITIALLY IMMEDIATE to set the starting state of the constraint.
The next lesson shows you how to find information about constraints in the data dictionary.