Re: adding a check condition

Vadim,

And when you have a table with 50 columns? that's an awfully big constraint...

and he specifically said "no blanks" not "not null"

>From: Vadim Gorbunov <dim_at_protek.ru>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: adding a check condition
>Date: Tue, 13 Jun 2000 22:45:26 -0800
>
>Hi, Rachel
>
>Rachel Carmichael wrote:
>
> > Is there some new syntax that I don't know of that does a table-level
>check
> > constraint?
> >
>
>I mean something like that
>
> create table t1(
> s1 varchar(10),
> s2 varchar(10),
> constraint c check (s1 <> ' ' and s2 <> ' '));
>
> The reason table vs column, of course, is to have as little dictionary
>entries as possible. I think all this consideration makes the sense ONLY
>when
>the table is iserted-updated very frequently - i.e. appropriate part of
>dictionary is effectively cached. PL/SQL will not beat constraint when the
>case.
>Listers, one more idea - why not NOT NULL (BTW - constraint too). It works
>with
>'' but not with ' '.
>
> With regards,
> Vadim
>
> >
> > a single PL/SQL program, even if it has to be parsed, will be faster
>than
> > reading the data dictionary for the constraint on every column. Check
> > constraints have their functions, and I have used them. But NOT to see
>if
> > all columns have blanks in them.
> >
> > Rachel
> >
> > >From: Vadim Gorbunov <dim_at_protek.ru>
> > >Reply-To: ORACLE-L_at_fatcity.com
> > >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > >Subject: Re: adding a check condition
> > >Date: Tue, 13 Jun 2000 04:57:04 -0800
> > >
> > >
> > >
> > >Rachel Carmichael wrote:
> > >
> > > > yes it adds to the overhead! and it will affect performance on every
> > >insert
> > > > and update. Oracle has to get the check condition from the data
> > >dictionary.
> > > >
> > > > since you want this check on EVERY column, it might be faster to do
>it
> > >all
> > > > at once in a before-insert/before-update trigger.
> > > >
> > >
> > >Hi, all
> > >I think, trigger is slower and more expensive than check constraint(s)
> > >because
> > >of PL/SQL engine is not as much efficient as Oracle core functions. One
> > >table
> > >level check constraint would be the most effective solution.
> > >
> > >Vadim Gorbunov
> > >Oracle DBA
> > >
> > > >
> > > > Rachel
> > > >
> > > > >From: "Anand Raman" <araman_at_india-today.com>
> > > > >Reply-To: ORACLE-L_at_fatcity.com
> > > > >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > > >Subject: adding a check condition
> > > > >Date: Mon, 12 Jun 2000 22:57:04 -0800
> > > > >
> > > > >Hi guys
> > > > >In a database which i am desiging to prevent users from entering
>blank
> > > > >spaces into columns i am also
> > > > >adding a check condition like check column <> ' ' to almost every
> > >column..
> > > > >Does this add to the overhead of the table and will it have any
> > >performance
> > > > >effect ..
> > > > >Thanx
> > > > >Anand
> > > > >
> > > > >--
> > > > >Author: Anand Raman
> > > > > INET: araman_at_india-today.com
> > > > >
> > > > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > > >San Diego, California -- Public Internet access / Mailing
>Lists
> > > >
> >--------------------------------------------------------------------
> > > > >To REMOVE yourself from this mailing list, send an E-Mail message
> > > > >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > > >the message BODY, include a line containing: UNSUB ORACLE-L
> > > > >(or the name of mailing list you want to be removed from). You may
> > > > >also send the HELP command for other information (like
>subscribing).
> > > >
> > > >
>________________________________________________________________________
> > > > Get Your Private, Free E-mail from MSN Hotmail at
>http://www.hotmail.com
> > > >
> > > > --
> > > > Author: Rachel Carmichael
> > > > INET: carmichr_at_hotmail.com
> > > >
> > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > > San Diego, California -- Public Internet access / Mailing
>Lists
> > > > --------------------------------------------------------------------
> > > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > > (or the name of mailing list you want to be removed from). You may
> > > > also send the HELP command for other information (like subscribing).
> > ><< dim.vcf >>
> >
> > ________________________________________________________________________
> > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> >
> > --
> > Author: Rachel Carmichael
> > INET: carmichr_at_hotmail.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
><< dim.vcf >>
Received on Wed Jun 14 2000 - 04:03:38 CDT