Back to cookbooks list Articles Cookbook

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:

CONSTRAINT_NAMECONSTRAINT_TYPETABLE_NAME
SYS_C007376RSTUDENT
SYS_C007374PSTUDENT
SYS_C007375USTUDENT
SYS_C007372CSTUDENT

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.

Recommended courses:

Recommended articles:

See also: