Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Not needed check constraints

From: Paul Brewer <>
Date: Thu, 25 Jul 2002 01:20:16 +0100
Message-ID: <>

"Richard Foote" <> wrote in message news:2cy%8.43147$
> Hi Stjepan,
> I guess it kind of depends.
> In my teeny weeny opinion, I would have the check constraint for a number
> 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.
> 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" <> wrote in message
> news:ahm5ud$fitv$
> > 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