How to delimit a phone field (Oracle Database)

Asked

Viewed 96 times

1

Good morning,

I am in need of a help to delimit the phone field in my database, being more specific, if today someone type a phone number with 4 digits (for example) that phone will be saved, I need to be accepted only numbers between 8 and 9 digits, I am using Oracle 11g.

database oracle oracle11g
  • 1

    Can’t this be done in the application? Do you have to deal with it in the BD? Da to use one trigger of before insert, if you really need to do it in the comic book.

1 answer

1


One solution may be to create a check Constraint.

For example

ALTER TABLE  CLIENTES
ADD CONSTRAINT CHK_CLIENTES_TEL
  CHECK (TELEFONE IS NULL OR LENGTH(TRIM(TELEFONE)) IN (8,9));

Here for example will be allowed to null phone , client has no phone , etc, but if filled must have size 8 or 9.

  • So dude I was trying something GOOD but not with the same syntax and was getting an error, making this cool you describe I get the following error: ORA-02293 is not possible to validate (chk_clients) - verification restriction violated

  • 1

    And that there must be values in the PHONE field that violate CONSTRAINT , 'clean" these fields before creating the same.

Browser other questions tagged database oracle oracle11g

You are not signed in. Login or sign up in order to post.