Finding the Primary Key and other constraints of a table in Oracle

Here's a quick query to help you find the columns involved as primary keys for a table in Oracle.

select * from user_cons_columns ucc, user_constraints uc

where uc.constraint_name=ucc.constraint_name

and uc.constraint_type='P'

and uc.table_name = 'EMP';

It seems that all the constraints for the tables in an oracle database are defined in the table user_constraints.

desc user_constraints;


select * from user_constraints;

To Find the distinct constraint types on a given table

select distinct constraint_type from user_constraints
where table_name='EMP';

When you execute the above query, you get a set of constraint codes that are used for the table in oracle.

R = Referential Constraint/Foreign Key constraint

C = Check Constraint

P = Primary Key

U = Unique Key

Now that you know what oracle constraints are defined on the table, you may also want to know the columns on which these constraints are actually defined. These come in handy mostly for determining the primary key, or the foreign key of a table.

For example, to determine all the columns in a table that have any constraints defined on them you can use

select * from user_cons_columns where table_name = 'EMP';

For additional information, you can take a look at the links mentioned in the references.

References

http://psoug.org/reference/constraints.html

http://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdition/Oracle/user_guide/oracle_guide.html

Happy Programming :)

Signing Off

Ryan