Constraint In Oracle

Constraint is use to restrict the data in database and it is use to validate the business rule.

As per the Oracle we have six type of constraint

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
6. REF

Now see the Primary key and Foreign key relation in  Oracle default table EMP,DEPT and SALGRADE.

Few Thing which we need to know in CONSTRAINT :

• This six constraint we can declare in Column level (IN LINE) and Table level (OUT OF LINE). But NOT NULL Constraint is the only constraint which should be declare in Column Level.

• Constraint clause can appear in CREATE OR ALTER TABLE or VIEW.

• Oracle don't support constraints on column whose data type is USER DEFINE OBJECTS , NESTED TABLE , VARRAY, REF AND LOB. But NOT NULL constraint supported for USER DEFINE OBJECTS, VARRAY, REF and LOB.

How to manage the Constraint:

1. Add Constraint :

Constraint can be added to a table any time after table was create or while creating table we can create the constraint.

Syntax:

ALTER TABLE <TABLE_NAME>

ADD CONSTRAINT <CONSTRAINT_NAME> CONSTRAINT_TYPE(COLUMN_NAME);

Restrictions :

• NOT NULL can be added to existing column by using the MODIFY clause of ALTER TABLE statement.
• We can add NOT NULL constraint when the table contains no rows.

2. Drop Constraint:

The CASCADE option of the DROP clause causes any dependent constraint also also to be dropped.

Syntax:

ALTER TABLE <TABLE_NAME> DROP PRIMARY KEY/UNIQUE(column)/CONSTRAINT  CONSTRAINT_NAME [CASCADE ];

3. ENABLE or DISABLE constraint:

Constraint can be ENABLE with out dropping the or recreating the constraint.

Syntax:

ALTER TABLE <TABLE_NAME> ENABLE/DISABLE CONSTRAINT <constraint _name>

4. Find the Constraint:

We can find the constraint from below Data Dictionary

• USER_CONSTRAINTS
• USER_CONS_COLUMNS

More Practice