Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Confused about "check" style constraunts

Re: Confused about "check" style constraunts

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 25 Aug 2001 23:44:14 +0200
Message-ID: <tog73r6nl0uc7@news.demon.nl>

"Stan Brown" <stanb_at_panix.com> wrote in message news:9m08p0$330$1_at_panix2.panix.com...
> In <to5f39lfa7b5ae_at_news.demon.nl> "Sybrand Bakker"
<postbus_at_sybrandb.demon.nl> writes:
>
>
> >"Stan Brown" <stanb_at_panix.com> wrote in message
> >news:9luda2$82j$1_at_panix3.panix.com...
> >> In <to59ms694kgg1f_at_news.demon.nl> "Sybrand Bakker"
> ><postbus_at_sybrandb.demon.nl> writes:
> >>
> >>
> >>
> >> >2 Please download Toad from www.toadsoft.com
> >> >run this query in a sql window, you will get a spreadsheet like
output.
> >> >Right click on the output window, export it to csv, and post it here.
> >> >Alternatively start a schema browser, and export the table definition
to
> >a
> >> >text file.
> >>
> >> Hmm, a quick glance at this site leads me to the conclusion this only
runs
> >> on some sort of Microsoft OS, am I correct? If so, I can't use it. How
> >else
> >> can I provide you with the data you need to help me understand the
> >problem?
> >>
> >>
> >>
> >> >You query is correct, but I can't imagine why on earth you would have
> >that
> >> >many check constraints.
> >>
> >> Hmm, let me check some other tables.
>
> >set colsep "," long 1000
> >spool check.csv
> >select table_name, constraint_name, search_condition
> >from ....
>
> >spool off
>
> >should do
>
> >Hth,
>
> >Sybrand Bakker, Senior Oracle DBA
>
> >should do
>
> OK here we go. First heres the query I ran:
>
>
> select
> C.CONSTRAINT_NAME,
> C.table_name,
> CC.COLUMN_NAME ,
> SEARCH_CONDITION
> from
> USER_CONSTRAINTS C ,
> USER_CONS_COLUMNS CC
> where C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
> AND CONSTRAINT_TYPE = 'C'
> AND C.TABLE_NAME = 'BRKR'
> ORDER BY CC.COLUMN_NAME
> /
>
>
>
> And here's what I got:
>
>
> SQL> @s
>
> CONSTRAINT_NAME ,TABLE_NAME
> ------------------------------,------------------------------
> COLUMN_NAME
> ------------------------------
> SEARCH_CONDITION
> --------------------------------------------------------------------------



> SYS_C003935 ,BRKR
> BOUGHT
> bought IN(0,1)
>
> SYS_C003938 ,BRKR
> BOUGHT
> bought IN(0,1)

[nsnip]
SYS_C003941                   ,BRKR

> GEN
> gen IN(0,1)
>
>
> 549 rows selected.
>
> SQL> spool off

WOW
(sorry for not following up sooner)
The common denominator is they are all constraints with system generated names.
You never used
create table
...
column constraint <meaningfulname> check in (0,1) or
alter table add constraint <constraintname> check

Then it looks like (guessing a bit here) you messed with import: you import a table multiple times. This resulted in duplicate constraints being created. This might be considered a bug.
So the good news is:
your query is correct
The bad news is:
You do have 549 contraints on one table. I have to think about how to get rid of them easily.

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Sat Aug 25 2001 - 16:44:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US