How to Find the Name of a Constraint in Oracle

Problem:

You want to find the names of the constraints in a table in Oracle.

Example:

We want to display the names of the constraints in the table student.

Solution:

select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
from USER_CONSTRAINTS
where TABLE_NAME='STUDENT';

Here is the result:

Discussion:

In Oracle, use the view user_constraints to display the names of the constraints in the database. The column constraint_name contains the name of the constraint, constraint_type indicates the type of constraint, and table_name contains the name of the table to which the constraint belongs. In the column constraint_type, the value “R” is for the foreign key, “P” is for the primary key, “U” is for the uniqueness constraint, and “C” is for the constraint check. In our example, there is a constraint in the table student of the type P (primary key) whose name is SYS_C007374.