Re: Confused about "check" style constraunts

Hi Stan,

I'd bet even Jonathan's salary (kidding Jonathan :) that you've got a cartesian product; at least you imply that because you state one of the tables has only 62 rows...depending on what you need, try starting with something like:

SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'C' ; Then just add more columns to your select clause. Or, if you need to join user_constraints with user_cons_columns, just use one join condition like,

WHERE C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME; and forget all the others.

Hope this helps,

Steve

"Stan Brown" <stanb_at_panix.com> wrote in message news:9ltp6v$q63$1_at_panix1.panix.com...
> I am trying to write a query that will get me information about any
"check"
> style constraints taht may exist on a given table.
>
> I thought that this query would do it:
>
>
> select
> C.CONSTRAINT_NAME,
> C.table_name,
> CC.COLUMN_NAME ,
> SEARCH_CONDITION
> from
> USER_CONSTRAINTS C ,
> USER_CONS_COLUMNS CC
> where CONSTRAINT_TYPE = 'C'
> AND C.TABLE_NAME = 'BRKR'
> AND C.TABLE_NAME = CC.TABLE_NAME
> AND C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
> AND C.OWNER = CC.OWNER
> ORDER BY CC.COLUMN_NAME
> /
>
>
> But it returned 540+ rows!
>
> Amazing considering that there are only 62 rows in the table!
>
> So, I tried this:
>
>
>
> select
> count(CONSTRAINT_NAME)
> from
> USER_CONSTRAINTS C
> where CONSTRAINT_TYPE = 'C'
> AND TABLE_NAME = 'BRKR'
> /
>
>
> COUNT(CONSTRAINT_NAME)
> ----------------------
> 549
>
>
> Here is the DDL that creates the brkr table:
>
>
> 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 ;
>
> So, can anyone explain mny misunderstanding hre? And perhaps sugest a
query
> that will get the information that I need?
>
Received on Tue Aug 21 2001 - 12:01:48 CDT