How to view check constraint properties on column

Posted on 2008-11-14
Last Modified: 2013-12-07
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?
Question by:digarati
    LVL 27

    Accepted Solution

    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>')
    LVL 4

    Assisted Solution

    select * from user_constraints where table_name=upper('tablename');
    LVL 28

    Assisted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    Via a live example, show how to take different types of Oracle backups using RMAN.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now