How to view check constraint properties on column

I have an existing database that was created by someone else.  I want to view the check constraint properties of a couple of columns.  How can I do this via command?
LVL 1
digaratiAsked:
Who is Participating?
 
SujithConnect With a Mentor Data ArchitectCommented:
You can make use of the views
dba_constraints
dba_cons_columns

If you dont have privileges you may use all_constraints and all_cons_columns

The query will look like

select *
from dba_cons_columns
where table_name = upper('<your table name>') and column_name = upper('<your column name>')
0
 
iozturkConnect With a Mentor Commented:
Use
select * from user_constraints where table_name=upper('tablename');
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
if you have a referential constraint, meaning a particular column in a table
is referring to a primay key column in some other table. For example, EMP table
has a column deptno with a referential constraint referring to deptno column in
DEPT table which is a primary key.

select *
from dba_constraints
where owner ='SCOTT'
and table_name ='EMP'; --> take the r_constraint_name column value from the
                       --> output of this query and put it in the below query
                       --> to see to which table column it is referring to.

select *
from dba_constraints
where owner ='SCOTT'
and constraint_name ='FK_DEPT_CONS' ; --> you can find all other details except
                                      --> column name of the the referential constraint.

select *
from dba_cons_columns
where constraint_name = 'PK_LOPV' ; --> you can find the column name
                                   --> of the referential constraint

Also the constraint type will be 'R' for foreign key constraints, 'P' for
primary key constraint, 'C' for check constraints.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.