Confused about "check" style constraunts

From: Stan Brown <stanb_at_panix.com>
Date: 21 Aug 2001 09:54:39 -0400
Message-ID: <9ltp6v$q63$1@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 - 08:54:39 CDT