Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Confused about "check" style constraunts
"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_C003941 ,BRKR
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