Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: How limit datatype......

    hi,
    I have this col in table tab_X:

    X_ID........varchar2(32)

    I'd like to limit the insert at max 8 characters.

    For example:
    insert into tab_X (X_ID) values ('00112233');

    correct

    insert into tab_X (X_ID) values ('001122334455');

    incorrect (error)

    How can I limit X_ID at max length=8??

    Thanks

  2. #2
    Join Date
    Oct 2003
    Posts
    87
    First, if you can, change the datatype of the column to number(8,0) else, you'll have to write a procedure to check the length, returning a user error if it exceeds 8 bytes.
    Oracle - DB2 - MS Access -

  3. #3
    Join Date
    May 2004
    Location
    Elmira, NY USA
    Posts
    5
    raf, maybe you don't want to change the length of the column for the data that's already there.

    Sounds to me like a job for a trigger. Just write a BEFORE INSERT trigger to and test for a LENGTH not greater than what you want, and possibly truncate the data, or an AFTER INSERT trigger to delete the row that you just added.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Alternatively, again if existing data can exceed 8 chars but new data cannot, then:

    alter table tab_x add constraint x_id_chk check (length(x_id) <= 8) enable novalidate;

    This will validate new data, but not existing data:

    Code:
    SQL> select * from tab_x;
    
    X_ID
    --------------------------------
    1234567890123456789
    
    SQL> insert into tab_x values ('1234567890123456');
    insert into tab_x values ('1234567890123456')
    *
    ERROR at line 1:
    ORA-02290: check constraint (TANDREWS.X_ID_CHK) violated
    
    
    SQL> insert into tab_x values ('12345678');
    
    1 row created.
    
    SQL> select * from tab_x;
    
    X_ID
    --------------------------------
    1234567890123456789
    12345678
    
    2 rows selected.

Posting Permissions

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