Back to cookbooks list Articles Cookbook

How to Find the Name of a Constraint in Oracle

  • CONSTRAINT
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • DEFAULT
  • CHECK

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

Recommended courses:

Recommended articles:

See also: