Constraints

In Oracle, constraints are a facility to enforce rules to make sure that only allowable data values are stored in the database(i.e to make sure the data integrity in database). All constraints have a name. The developer who defines the table or adds a constraint can name it at that time. However, if the name is not supplied, Oracle will assign a system generated name that will uniquely identify the constraint. System generated names are pre-fixed with SYS_C (for "system constraint") followed by a 7 digit integer.

Types of Constraints:

1. NOT NULL: If as per business logic, a column or a set of columns in a table can not allow NULL values, then NOT NULL constraint can be used to enforce this rule.

e.g

alter table SALES modify (cust_id NOT NULL);

2. UNIQUE: If as per business logic, a colum or a set of columns in a table need to store unique values, then, UNIQUE constraint can be used to enforce this rule.

e.g

create table test (col1 number UNIQUE);

UNIQUE constraints allow NULL values to be stored.

3. PRIMARY KEY : Primary Key constraint is a combination of NOT NULL and UNIQUE constraints. The column or the set of columns on which Primary Key is defined, will allow only unique and not null values. There can only be 1 (and only 1) primary key in an Oracle table.

e.g

create table test (col1 number PRIMARY KEY);

4. FOREIGN KEY:  It is frequenly required that data in one table should be validated by comparing it to data in other table.(e.g if you add a new order in your ORDERS table, you must cross check that a valid product corresponding to this order is present in your PRODUCTS table). To achieve this kind of data integrity, foeign key constrained is used. This type of validation is also known as referential integrity. A foreign key constraint always makes refrence to a Primary key or a unique constraint of other table. The table that has foreign key defined is called referencing table. The table that has Primary key or Unique constraint defined is called referenced table.

e.g

create table orders
(
  order_no number primary key,
  customer_name varchar2(10),
  constraint cons_prod_fk foreign key(prod_no) references product(prod_no)
);

> If you define the foreign key constraint with 'ON DELETE CASCADE' option, then if any rows are deleted from the referenced table, then the corresponding rows will also be deleted from the referencing table.

> NULL values are allowed in Foreign Key columns.

5. CHECK:  Check constraints are used to enforce one or more conditions to be checked for the data row.

e.g

alter table customers add constraint customer_credit_limit CHECK (credit_limit <= 1000)

Some additional information regarding Constraints:

• Constraint names can be found in ALL_CONSTRAINTS table. The column names on which constraints are defined can be found in ALL_CONS_COLUMNS.

• Constraints can, at any time, be either enabled or disabled. When you create, disable or enable a constraint, you may speify some other information regarding how the constraint behaves. An ebabled constraint can have two options VALIDATE and NOVALIDATE. VALIDATE will validate the existing data in the table while NOVALIDATE will not validate the existing data afyter the constraint is enabled.

• When you create or enable a Primary key or Unique constraint, Oracle will create a unique index on the columns of that constraint. Foreign key constraints do not enforce an automatic creation of index.However it is worthwhile to build an index on the columns of each foreign key constraint. Without an index on the corresponding columns in the child table, Oracle is forced to take out a table lock on the child while it performs the DELETE on the parent. If an index is existing, Orcale uses it to identify and lock just the necessary rows in the child while the parent row is deleted.