Re: Not needed check constraints

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
>
>
>
>
>
Received on Wed Jul 24 2002 - 08:53:13 CDT