Re: Constrainting NUMBER

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 26 May 2005 06:58:39 -0700
Message-ID: <1117115655.348971@yasure>

as wrote:

> "DA Morgan" <damorgan_at_psoug.org> ??? news:1117085511.454471_at_yasure ???...
>

>>Best practice is to enforce data integrity in everything you do.
>>That means define your data types after careful consideration and
>>study of the data they will be asked to hold.
>>
>>The alternative is to do the following:
>>CREATE TABLE bad_practice (
>>person_id NUMBER,
>>first_name VARCHAR2(4000),
>>last_name VARCHAR2(4000),
>>address VARCHAR2(4000),
>>socsecno VARCHAR2(4000));
>>
>>does that make any sense? A Social Security Number of up to 4000
>>bytes?
>>--

> 
> Well, putting unconstrainted VARCHAR2 is definitely a bad practice -
> consider how sqlplus format the column.  But for NUMBER, I do come across a
> textbook suggesting not to constraint it unless there is some good reasons -
> however the reasons are not clearly explained. (If you want to know, it is
> Oreilly's Java Programming with Oracle JDBC by Donald Bales Chap 10.2.)

I've come across books with all kinds of fascinating advice. Taking that advice, however, requires application of synapses.

Take for example a date column. Would you let someone enter a DOB (date of birth) greater than the current date? How about one more than 150 years ago? ... I know depends on the system ... but at least you thought about it.

Lets say the column holds person's ages. Is there any value in NUMBER(4)? How about money? Is there any value in allowing more than two decimal points? ... Again ... it depends on the system ... but you need to think about it.

In my opinion there is a word for people that use NUMBER without application of gray matter ... the word is lazy. And why not consider the data type of FLOAT? Or REAL? The correct tool is now always a hammer.

-- 
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Thu May 26 2005 - 08:58:39 CDT