Check the Constraints in Oracle database

Check the constraints present in table

Select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE from DBA_CONSTRAINTS where table_name='EMP';

Note: Constraint_type column value specify the constraint type:
C (check constraint a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (read-only on view)

Check constraint is enable or disable

Select CONSTRAINT_NAME,STATUS from DBA_CONSTRAINTS where table_name='EMP';


Check the CHECK constraint condition

select CONSTRAINT_NAME,CONSTRAINT_TYPE ,SEARCH_CONDITION from DBA_CONSTRAINTS where table_name='EMP';


Check column involved in Constraint

SELECT c.constraint_name, c.constraint_type, c.r_constraint_name,
c.table_name, cc.column_name, cc.position, c.search_condition
FROM all_constraints c, all_cons_columns cc
WHERE c.table_name = 'EMP'
AND c.constraint_name = cc.constraint_name;


Check detail of Foreign Key Constraints

SELECT cf.constraint_name "FOREIGN KEY",
cp.constraint_name "DEPENDS ON",
cp.table_name, ccp.column_name, ccp.position
FROM all_constraints cp, all_cons_columns ccp, all_constraints cf
WHERE cp.table_name = '&tableName'
AND cp.constraint_name = ccp.constraint_name
AND cf.r_constraint_name = cp.constraint_name
AND cf.r_constraint_name = ccp.constraint_name;


Types of Constraint and Explanation

Not Null
The NOT NULL constraint enforces a column to NOT accept NULL values.
This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

Example

Primary Key
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.A table can have only one primary key, which may consist of single or multiple fields.

Example

Unique Key
The UNIQUE constraint ensures that all values in a column are different.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Example

Check
The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Example

Foreign Key
A FOREIGN KEY is a key used to link two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table

Example