Display names of all constraints for a table in Oracle SQL

In-depth lookup strategy

Sure, a simple list is helpful. But how about we dig deeper for a more comprehensive understanding?

Constraints in other schemas

Not dealing with your personal schema? Swap user_constraints with all_constraints and include the schema owner:

SELECT constraint_name, constraint_type
FROM all_constraints
WHERE table_name = 'TABLE_NAME' AND owner = 'SCHEMA_OWNER'; -- Share the love, include others' schemas


'Mr or Mrs SCHEMA_OWNER' should be replaced with the actual owner of the schema.

Constraint details

To fetch all the nitty-gritty like column names, merge user_constraints and user_cons_columns:

SELECT uc.constraint_name, uc.constraint_type, ucc.column_name
FROM user_constraints uc
JOIN user_cons_columns ucc ON uc.constraint_name = ucc.constraint_name
WHERE uc.table_name = 'TABLE_NAME'; -- Spill all the beans, why don't we?


This fetches you a screen full of delicious constraint details. Bon Appétit!

Specific constraint types

In case you don't want the whole buffet and need a particular kind of constraint:

SELECT constraint_name 
FROM user_constraints 
WHERE table_name = 'TABLE_NAME' AND constraint_type = 'P'; -- Only the Prime (P=Primary Key, get it?)


Missing constraint mystery

Can't spot your constraint? Check the following:

• Is the table name correct and capitalized? If not, uppercase it.
• Are you in the right schema? If in doubt, use all_constraints.
• Was the constraint dropped? Better check with Sherlock Holmes (your database admin).

Navigating across schemas

Working with multiple schemas? Tack on the schema to the table name:

SELECT ... FROM all_constraints WHERE table_name = 'SCHEMA.TABLE_NAME'; -- Be mindful of ALL the schemas!


Troubleshooting potential issues

• Insufficient privileges: If your query returns an error, you may need additional privileges. It's not you, it's your role.
• Understanding constraint types: Don't mix up your Ps and Us...Primary key (P) isn't the same as unique constraint (U).