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