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 UNIQUE constraint, and C is for the CHECK constraint. For example, in the table student there is a constraint of the type P (primary key) whose name is SYS_C007374.