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); 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 ( alter table employee add constraint gender_chk check(gender in('M','F')); |
Unique constraints | create table dept ( alter table dept add constraint dept_idx unique(dept_no); |
Referential integrity constraints | create table employee ( 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 ( Note: by default oracle set it as not deferrable |
deferrable | create table employee ( set constraint <constraint name> deferred; |