Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query to select "check" constraints?

Re: Query to select "check" constraints?

From: Stan Brown <stanb_at_panix.com>
Date: 17 Aug 2001 09:09:43 -0400
Message-ID: <9lj52n$1fa$1@panix2.panix.com>


In <9lj161$oqe$1_at_panix2.panix.com> stanb_at_panix.com (Stan Brown) writes:

>In <9lhjap$e1e$1_at_panix2.panix.com> stanb_at_panix.com (Stan Brown) writes:

>>In <998001398.13088.0.pluto.d4ee154e_at_news.demon.nl> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> writes:

>>>"Stan Brown" <stanb_at_panix.com> wrote in message
>>>news:9lhbb8$64c$1_at_panix1.panix.com...
>>>> How can I select the "check" constraints defined at table creation time
>>>> with an SQL query, given the table name.
>>>>
>>>> I would like to know what collumn(s) have check constraints, and what
>>>those
>>>> constraints are (as in what are legal values etc).
>>>>
>>>>
>>>>

>>>select table_name, constraint_name, check_condition
>>>from user_constraints where constraint_type = ' C'

>>Thanks, I should have know that the oracle of Oracle would come thorugh!

>Much to my suprise, this does not work.

>Here is the structuer of user_constraints on my instance (7.3.4)

>SQL> describe user_constraints ;
> Name Null? Type
> ------------------------------- -------- ----
>OWNER NOT NULL VARCHAR2(30)
>CONSTRAINT_NAME NOT NULL VARCHAR2(30)
>CONSTRAINT_TYPE VARCHAR2(1)
>TABLE_NAME NOT NULL VARCHAR2(30)
>SEARCH_CONDITION LONG
>R_OWNER VARCHAR2(30)
>R_CONSTRAINT_NAME VARCHAR2(30)
>DELETE_RULE VARCHAR2(9)
>STATUS VARCHAR2(8)

>Do I need to join to some other view?

OK, I'm doing something wrong here. I tried:

select
C.table_name,
CC.COLUMN_NAME
from
USER_CONSTRAINTS C ,
USER_CONS_COLUMNS CC
where CONSTRAINT_TYPE = 'C'
AND CC.TABLE_NAME = 'BRKR'
AND CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME ORDER BY CC.TABLE_NAME And got 500+ rows!

I must be mucking up the join, soehow? Received on Fri Aug 17 2001 - 08:09:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US