Re: Restrict column entry to integers

>>>>> "Dan" == Dan Bowen <dbowen_at_empros.com> writes:
In article <CJH7EC.I7L_at_empros.com> dbowen_at_empros.com (Dan Bowen) writes:

  Dan> I have a small database that will be accessed by SQL*Loader,
  Dan> SQL*Plus and FORMS (probably v3).  There are several columns
  Dan> that MUST contain integers.  I do not want to have ORACLE round
  Dan> a number with a scale of zero for me. I'd like to raise an
  Dan> exception whenever a user tries to use a decimal point.  I can
  Dan> do the restriction using FORMS. Can I do it using CONSTRAINTS?

Yes. See below. Oracle only has one internal format for numeric data. When specifying a scale (or using integer = number(x,0)), you are just defining how data in some external data-format will be converted to Oracle's (generic) internal format.

  Dan> The only methods I've thought of are 1) a trigger to convert a
  Dan> character column into a number Pro: **Does what I want (raise
  Dan> an exception when necessary) **Can catch ALL entries of
  Dan> non-integer values (even with a single trailing decimal point)
  Dan> Con: **demands extra storage space **possible confusion among
  Dan> users about which column name to use in SELECT... statements
  Dan> **more coding than I'd like to maintain (I have several dozen
  Dan> columns which need the integer restriction)

And great performance when you disable any index on a char/varchar2 col by comparing it to an integer. Don't do this.

  Dan> 2) change the scale on the 'integer' columns to be non-zero and
  Dan> then create a constraint that checks (column=TRUNC(column)) or
  Dan> (column=ROUND(column)) Pro: **Does most of what I want (doesn't
  Dan> catch 1.0000 as not an integer) **allows naming the constraints
  Dan> to help identify the exception Con: **AdHoc sqlplus querys show
  Dan> the 'integers' with non-zero scale confusing the users who
  Dan> 'know' which columns are 'integer' values.  **Depending on the
  Dan> scale chosen, may still not trap all instances.  If ORACLE
  Dan> still rounds, it may round to an integer depending on the input
  Dan> values.

Huh ? 1.0000 *is* exactly the same thing as 1 in an Oracle database (unlike C and other languages). Don't confuse this with "1.0000" and "1" in some external format - say char[] or int/float.

If you want to restrict how external data is passed to Oracle, you must do it in your application. Here's how you can guarantee an error if anything tries to insert a "float" (Forms v3/v4 can auto-generate on-validate-field triggers from this constraint):

SQL> create table all_int
  2 ( x number,
  3 constraint x_must_be_an_integer check ( x = floor(x) ) );

Table created.

SQL> insert into all_int values ( 5.4 ); insert into all_int values ( 5.4 )

     *
ERROR at line 1:
ORA-02290: check constraint (SCOTT.X_MUST_BE_AN_INTEGER) violated

I think most users will understand what the problem is here. Note that if the table was defined as ( x integer ) you would not get any error, since external->internal conversion is done (5.4->5) before any constraints are applied.

  Dan> I'd appreciate any help possible. I've already talked to   Dan> ORACLE about this with less than stupendous success :-)

  Dan> I'll post the best method suggested when I'm less than giraffe
  Dan> deep in work that was scheduled to be done (long ago and far
  Dan> away...)

  Dan> Thanks in advance.......

  Dan> D.J.Bowen | dbowen_at_empros.com Empros Power Systems Control |
  Dan> Siemens Energy and Automation, Inc.  2300 Berkshire Lane North
  Dan> | Voice (612) 553-4530 Plymouth, MN 55441-3694 | Fax (612)
  Dan> 553-4799

--

Magnus Lonnroth
Tech.Sales & Consultant
Oracle Swed
--

Magnus Lonnroth
Tech.Sales & Consultant
Oracle Sweden
Mail: mloennro_at_se.oracle.com