edwardsearch
asked on
list Table Constraints
how to view one particular table constraints in oracle
For example i want to view all primary,unique,integrity constraints in emp table
For example i want to view all primary,unique,integrity constraints in emp table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To get both,
select constraint_name
from all_constraints a
where table_name = 'EMP'
and constraint_type = 'P'
and exists
(select 1
from all_constraints b
where table_name = 'EMP'
and constraint_type = 'U'
and a.constraint_name = b.constraint_name);
select constraint_name
from all_constraints a
where table_name = 'EMP'
and constraint_type = 'P'
and exists
(select 1
from all_constraints b
where table_name = 'EMP'
and constraint_type = 'U'
and a.constraint_name = b.constraint_name);
The Constraint_type column in those data dictionary is used to determine the type of constraint.
P - Primary Key
U - Unique Key
so you write
Select * from all_constraints
where table_name = 'EMP'
and Constraint_Type in ('P','U');
Thanks
kamal