Not needed check constraints

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 24 Jul 2002 14:40:13 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702699C5C@lnewton.leeds.lfs.co.uk>

Stjepan,

leave the check constraint applied.

It enforces the Y or N values only regardless of how the data is stored in the database.

Imagine, your application and what would it do if I logged into SQLPLus and run the command "update ... set active = 'X'; " The application still forces Y or N but all of a sudden there is a X in the database.

Always assume that someone, somewhere will use SQLPlus or TOAD or some other method of 'fixing' the data in your database, you might even do it yourself. If you have the constraints built into the database, then nobody will be able to put duff data in the tables - unless they turn off the constraints of course.

Cheers,
Norman.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

-----Original Message-----

From: Stjepan Brbot [mailto:stjepan.brbot_at_zg.hinet.hr] Posted At: Wednesday, July 24, 2002 12:54 PM Posted To: server
Conversation: Not needed check constraints Subject: Not needed check constraints

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:40:13 CDT