[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5845
  • Last Modified:

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?
3 Solutions
You can make use of the views

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>')
select * from user_constraints where table_name=upper('tablename');
Naveen KumarProduction 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.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now