How to get list of all constraints present in a table in Oracle? [duplicate]

Question

I have a table named STUDENT with following attributes:

enroll VARCHAR(20), 
name VARCHAR(50), 
age INT, 
fees FLOAT(6,2)

Now I want to know that which all constraints (like Primary Key, Unique Key, NOT NULL, etc) are present in this table. Kindly let me know how can I get this?

Answer 1

You can use the JOIN on two tables USER_CONSTRAINTS and USER_CONS_COLUMNS to fetch the Column Name, Constraint Type and Table Name.

SELECT ucc.COLUMN_NAME, uc.CONSTRAINT_TYPE ,uc.TABLE_NAME 
FROM USER_CONSTRAINTS uc  JOIN  USER_CONS_COLUMNS ucc 
ON uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME 
AND uc.TABLE_NAME=ucc.TABLE_NAME
WHERE uc.TABLE_NAME='mytablename';

Constraint Definition can be referred as:

C (check constraint on a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (with read only, on a view)

For more information you can view Oracle Documentation here