Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: A join question
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),
FOREIGN KEY (meter_id) REFERENCES meter, CHECK (gen IN(0,1)), CHECK (bought IN(0,1)), CHECK (consume IN(0,1))