Integrity Constraints

Oracle enforces important business rules via constraints, for instance all employees must have a national insurance number and a manager to report to, they force data to conform to certain rules. There are five different constraints

Primary key constraints A primary key is basically a key which is Not Null and Unique
Not Null constraints A tables column cannot be Null, it must contain a value
Check constraints Ensure that a tables column is within some parameters that you have specified, for example a employee's salary must not exceed £100,000.
Unique constraints Ensure the uniqueness of the rows in the table, for example national insurance number would be unique to each employee
Referential integrity constraints

Ensure that values for certain important columns make sense basically cross referencing other tables, for example confirm that the department exists in the department table. The reference is know as a foreign key and the table is the child table which the actual data held in the parent table.

It is possible for a table to have a self referential integrity constraint.


Examples
Primary key constraints

create table dept (dept_id number(9) primary key);
alter table dept add primary key(dept_id);
alter table dept add constraint dept_pk primary key(dept_id);
alter table user add constraint user_pk primary key(fname,lname);

Note: the last example shows that you can give the index a name instead of a system generated one.

Not Null constraints create table employee (nat_sur varchar(9) not null);
alter table employee modify dob not null;
Check constraints

create table employee (
 employee_id number,
 employee_name varchar(60),
salary number check(salary < 100000));

alter table employee add constraint gender_chk check(gender in('M','F'));

Unique constraints

create table dept (
 dept_no number(3),
 dept_name varchar2(15),
 location varchar2(25),
constraint dept_name_ukey unique(dept_name,location));

alter table dept add constraint dept_idx unique(dept_no);
alter table dept add constraint name_location_idx unique(dept_name,location);

Referential integrity constraints

create table employee (
 employee_id number(7),
 last_name varchar2(30),
 first_name varchar2(30),
 dept_id number(3) not null,
constraint dept_fk references dept(dept_id));

Note: To create a foreign key constraint on an object in a different schema you must have the REFERENCES privilege on the columns of the referenced key in the parent table or view.
Altering Constraints cannot be altered they must be dropped and created.
Remove a constraint

alter table <table name> drop constraint <constraint name>;

Rename a constraint alter table <table name> rename constraint <old name> to <new name>;
Enable/Disable a constraint alter table <table name> disable constraint <constraint name>;
alter table <table name> enable constraint <constraint name>;
Display constraint condition column search_condition format a50;
select constraint_name, constraint_type, table_name, search_condition from user_constraints;

Useful Views

DBA_CONSTRAINTS describes all constraint definitions on all tables in the database including the search condition.
DBA_CONS_COLUMNS You can query this view to find the names of constraints, what columns they affect, and other information to help you manage constraints

Contraint Code Types

When using the dba_constraints view you can select the constraint_type column to display the constraint type.

Code Type Description
P Primary Key
U Unique Key
C Check Constraint
R Referential Key (Foreign Key)
0 Read Only
V Check option on view

Constraint States

Sometimes when you are loading data you need to override the constraints , oracle allows you to disable the constraint thus speeding up the data loading, there are 4 states

Constraint States
Disable validate state alter table sales_data add constraint quantity_unique unique (prod_id,customer_id) disable validate;
Disable no validate state alter table sales_data add constraint quantity_unique unique (prod_id,customer_id) disable no validate;
Enable validate state alter table sales_data add constraint sales_region_fk foreign key (sales_region) references region(region_id) enable validate;
Enable no validate state alter table sales_data add constraint sales_region_fk foreign key (sales_region) references region(region_id) enable no validate;

Deferrable and Immediate Constraints

In Oracle you can specify when the constraint is to be checked after each modification (not deferrable) which is the default behavior in oracle or a one time check after the whole transaction is committed (deferrable). If you choose deferrable there are a further two options, initially deferred (will defer checking until the transaction completes) or initially immediate (check the constraint before any data is changed).

Not deferrable

create table employee (
employee_id number,
dept varchar2(30) unique references department(dept_name));

Note: by default oracle set it as not deferrable

deferrable

create table employee (
employee_id number,
dept varchar2(30) unique references department(dept_name) deferrable initially deferred);

set constraint <constraint name> deferred;
set constraint <constraint name> immediate;