Types of Constraints

Types of Constraints:

1. NOT NULL: 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 emp MODIFY (emp_id NOT NULL);

2. UNIQUE: 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.

Note:

i] Unique constraint can allow a null value.

ii]Unique constraint with NOT NULL can be as like P.K. since it cannot allow null values.

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. 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)

);

1] 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.

2]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)

More on Constraints:

• In ALL_CONSTRAINTS table - Constraint names can be found.
• In ALL_CONS_COLUMNS - Column names on which constraints are defined can be found.
•  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 enabled 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.