Results 1 to 7 of 7

Thread: vithalreddy

  1. #1
    Join Date
    Sep 2011
    Location
    Hyderabad
    Posts
    18

    Unanswered: vithalreddy

    How to restrict float no in primary key?
    example
    create table a(sno number(2));

    insert into a values(12.36) ;
    the insert should not insert a row into table i want to restrict
    insert into a values(12); now it should accept.

    oracle rounds the value before i know.
    sequences we will use to insert primary key values i know
    i need to restrict immidiate after entering float values.
    Last edited by vithalreddy; 10-01-11 at 05:11.

  2. #2
    Join Date
    Sep 2011
    Posts
    20
    The restriction could be redundant because running these insert statements will insert the value 12 into the NUMBER column for both rows.

    Code:
    CREATE TABLE table1
    (column1 NUMBER(2))
    
    insert into table1 values(12) ;
    insert into table1 values(12.36) ;
    In other words, whatever codes you add, whatever constraints you use, or you choose not to do anything, 12.36 will never be inserted, instead the value is truncated and a value of 12 will be inserted.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It appears that numbers are rounded before the value is stored into the table. It means that CHECK constraint won't catch these values, BEFORE INSERT trigger won't catch it. I can't think of other possibilities right now (if there are any).

    Here's an example (useless, I know):
    Code:
    SQL> -- what does the REGEXP_SUBSTR do:
    SQL>
    SQL> with test as
      2    (select 12.34 sno from dual union
      3     select 57.75 sno from dual
      4    )
      5  select sno,
      6         regexp_substr(sno, '[[:digit:]]+') r_sno
      7  from test;
    
           SNO R_SNO
    ---------- ----------
         12.34 12
         57.75 57
    
    SQL> -- it works OK. Now let's create a table that uses it in CHECK constraint
    SQL>
    SQL> create table test_1
      2    (sno number(2) check (sno = regexp_substr(sno, '[[:digit:]]+')));
    
    Table created.
    
    SQL> insert all
      2    into test_1 (sno) values (12.34)
      3    into test_1 (sno) values (8.78)
      4    into test_1 (sno) values (67.8)
      5  select * from dual;
    
    3 rows created.
    
    SQL> select * from test_1;
    
           SNO
    ----------
            12
             9
            68
    
    SQL>
    Let's try a trigger approach:
    Code:
    SQL> create table test_2
      2    (sno number(2));
    
    Table created.
    
    SQL> create or replace trigger trg_sno
      2    before insert on test_2
      3    for each row
      4  begin
      5    if :new.sno <> regexp_substr(:new.sno, '[[:digit:]]+') then
      6       raise_application_error(-20001, 'Invalid SNO');
      7    end if;
      8  end;
      9  /
    
    Trigger created.
    
    SQL> insert all
      2    into test_2 (sno) values (12.34)
      3    into test_2 (sno) values (8.78)
      4    into test_2 (sno) values (67.8)
      5  select * from dual;
    
    3 rows created.
    
    SQL> select * from test_2;
    
           SNO
    ----------
            12
             9
            68
    
    SQL>
    I failed, again.

    Now, maybe I'm doing it wrong and someone will fix it and actually answer your question.

    Basically, Oracle implicitly does what you'd want to enforce manually. I understand that this might be misleading (users enter 12.34, believe it is actually stored that way, but heck - it is not!). On the other hand, you could (probably) enforce that rule in front-end application, couldn't you?

    However: as you'd like to do that for a primary key column, think twice! I don't believe that you should let users enter primary key values. Choose something different (a sequence would be a good choice, for example). Let them enter those SNOs, no problem - enforce any integritry rule on them (such as them being unique, numerics only, fixed length, whatever). (Keyword: surrogate key).
    Last edited by Littlefoot; 09-30-11 at 16:02.

  4. #4
    Join Date
    Sep 2011
    Location
    Hyderabad
    Posts
    18

    vithal reddy

    thanks... you catch what i am expecting..
    but how to achieve it.
    is it possible to achive?
    ...
    check or trigger not catching entered number before rounding how to catch?

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by vithalreddy View Post
    check or trigger not catching entered number before rounding
    As they are called after this implicit conversion, they cannot.
    Just for completeness you may put strings to your NUMBER column:
    Code:
    insert into a values('12') ;
    The same reason - implicit conversion.
    Quote Originally Posted by vithalreddy View Post
    how to catch?
    It would be nice if you posted any feedback to the other thread on this forum where you placed the same question: http://www.dbforums.com/oracle/12020...ml#post6519996
    My answer is the same; maybe I would only suggest to rather change the SNO data type to NUMBER(38,36) to be able to reject e.g. '12.003' - a few users will insert 36 zeroes after decimal place and if anybody did, you may to suppose it to be integer anyway.

    Just curious - what real problem are you really trying to solve?
    Last edited by flyboy; 10-01-11 at 06:34. Reason: added missing word

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    why not make the column simply number with no restraint and have a check constraint

    Code:
    CREATE TABLE TEST
    (
      MY_VALUE  NUMBER
    );
    
    ALTER TABLE TEST ADD (CHECK (round(my_value) = my_value));
    
    
    >insert into test values(12);
    
    1 row created.
    
    >insert into test values(10.3);
    insert into test values(10.3)
    *
    ERROR at line 1:
    ORA-02290: check constraint (CUSTOM.SYS_C00694047) violated
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by beilstwh View Post
    why not make the column simply number with no restraint and have a check constraint
    Why not, if you will not forget the constraint about its length.
    Code:
    MY_VALUE between -99 and 99
    However it would be best to make all checks of user entered value on application side, not on database one.
    Despite it, I am not a fan of all these implicit conversions (leading to too many unexpected behaviour).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •