Re: A join question

In <998084079.11287.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:9ljt02$d36$1_at_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?
>>

>You're now getting a cartesian product!
>and
>Look, here it is

>AND CC.TABLE_NAME = 'BRKR'
>AND CC.TABLE_NAME = C.TABLE_NAME

>should be
>AND C.TABLE_NAME = 'BRKR'
>AND CC.TABLE_NAME = C.TABLE_NAME
Ah, stupid of me.

Will this address teh multiplicity of constraint names mentioned in my other thread on this? Received on Fri Aug 17 2001 - 17:12:43 CDT