Re: A join question

From: Stan Brown <stanb_at_panix.com>
Date: 17 Aug 2001 15:57:54 -0400
Message-ID: <9ljt02$d36$1@panix1.panix.com>

In <9ljqab$n4g$1_at_panix3.panix.com> stanb_at_panix.com (Stan Brown) writes:

>I am trying to obtain the "check" constraints on a given table using an SQL
>statement. So far I have this:

>select
>C.table_name,
>CC.COLUMN_NAME ,
>SEARCH_CONDITION
>from
>USER_CONSTRAINTS C ,
>USER_CONS_COLUMNS CC
>where CONSTRAINT_TYPE = 'C'
>AND CC.TABLE_NAME = 'BRKR'
>AND CC.TABLE_NAME = C.TABLE_NAME
>AND CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
>ORDER BY CC.TABLE_NAME

>Which seems to return what I need BUT for a table that shold only have 2 or
>3 of these it returns 500+ rows (and takes a long time).

>I suspect that I am dooing the join incoreclty. Any guidance oon this?

OK, this just gets wierer. In playing around, I have determined that there are 551 different constraint names on this one table! Now realy there are only a couple here is the table create statement:

CREATE TABLE brkr (

brkr         VARCHAR(5)       NOT NULL ,
brkr_level   NUMBER (2,0)     NOT NULL ,
descript     VARCHAR(80)      NOT NULL ,
gen          NUMBER(1,0)      NOT NULL ,
bought       NUMBER(1,0)      NOT NULL ,
consume      NUMBER(1,0)      NOT NULL ,
meter_id     NUMBER (2,0),

CONSTRAINT brkr_index
PRIMARY KEY (brkr, brkr_level)
USING INDEX
TABLESPACE CONFIG_TS ,

FOREIGN KEY  (meter_id)       REFERENCES meter,
CHECK        (gen IN(0,1)),
CHECK        (bought IN(0,1)),
CHECK        (consume IN(0,1))

)
TABLESPACE CONFIG_TS ; And there are only 62 rows in the table, how can there be that many cobstraints on it? Received on Fri Aug 17 2001 - 14:57:54 CDT