rohit rohit - 1 month ago 6
SQL Question

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

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

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

Comments