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

x
  • 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?
0
digarati
Asked:
digarati
3 Solutions
 
sujith80Commented:
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
 
iozturkCommented:
Use
select * from user_constraints where table_name=upper('tablename');
0
 
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.
0

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