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

Home -> Community -> Usenet -> c.d.o.server -> Re: Not needed check constraints

Re: Not needed check constraints

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Thu, 25 Jul 2002 01:20:16 +0100
Message-ID: <3d3f6a6a_2@mk-nntp-1.news.uk.worldonline.com>


"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:2cy%8.43147$Hj3.130053_at_newsfeeds.bigpond.com...
> Hi Stjepan,
>
> I guess it kind of depends.
>
> In my teeny weeny opinion, I would have the check constraint for a number
of
> reasons.
>
> 1) In case it's possible for someone, or some other application to make
> direct changes to the data, the database constraint is a "second line of
> defence". Can you definitely guarantee that this application will be the
> only method that changes will ever be made to this table ?
>
> 2) Although you're right and there is some overhead in Oracle having to
> check this constraint, there could actually be some performance benefits.
If
> someone was (silly enough) to search for non 'Y' , 'N' values, without the
> check constraint, Oracle will have no choice but to read all the data. But
> with the check constraint, Oracle will be able to return no rows *without*
> hitting the data. An unlikely scenario but who knows ?
>
> 3) I see constraints as the implementation of business rules that governs
> the data. These business rules are thus documented in the database via the
> constraints. If you don't have the check constraint, this particular
> business rule is thus not documented and could cause issues at some later
> point in time (eg. exporting table definition to another database, reverse
> engineering of database, etc.)
>
> So my vote, create the constraint.
>
> Cheers
>
> Richard
> "Stjepan Brbot" <stjepan.brbot_at_zg.hinet.hr> wrote in message
> news:ahm5ud$fitv$1_at_as201.hinet.hr...
> > I have table with column "ACTIVE" where my application stores the values
> > of checkbox: 'Y' or 'N' (checked/unchecked). Although my application
> > cannot store any other value than 'Y' or 'N', question is; is it good
> > practice to have the additional check constraint checking if the value
> > of "ACTIVE" column is in domain ('Y','N') or this is just waste of
> > maintenance time and database performance because every time before
> > inserting or updating database has to check if the new data for "ACTIVE"
> > column is in ('Y','N') domain?
> >
> > --
> >
> > Stjepan Brbot
> >

My vote: Agree 100% with Norman and Richard. Enforce it on the database.
What can you (or for that matter the CBO) lose?

Paul Received on Wed Jul 24 2002 - 19:20:16 CDT

Original text of this message

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