1

I am trying to impose a constraint to fix the length of phone numbers to 12, but the code fails:

create table customer (
rollcall number CONSTRAINT rc_pk PRIMARY KEY,
fname varchar(2) not NULL,
lname varchar(2)  not NULL,
phone varchar2(13) check (length(phone))=12 
);
Mureinik
  • 297,002
  • 52
  • 306
  • 350
Nigel
  • 47
  • 1
  • 2
  • 8
  • Better to include the actual error message than to just say "the code fails" – Dave Costa Sep 07 '15 at 19:12
  • Error starting at line : 18 in command - create table customer ( rollcall number CONSTRAINT rc_pk PRIMARY KEY, fname varchar2(20) not NULL, lname varchar2(20) not NULL, phone varchar2(13) (check (length(phone)<=12) ) ) Error report - SQL Error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause: *Action: – Nigel Sep 07 '15 at 19:40
  • I would have tried insert before and update before triggers and handled this logic there.(you can append + or whatever from that). It would be better and you can disable/update them as per your convenience without touching the definition. Just a suggestion. – Utsav Sep 08 '15 at 01:14

1 Answers1

2

Just define the maximum length to 12 - it would be much easier:

create table customer (
    rollcall number CONSTRAINT rc_pk PRIMARY KEY,
    fname varchar(2) not NULL,
    lname varchar(2)  not NULL,
    phone varchar2(12)
);

EDIT:
Now that the question has been reworded to explain that the length of the field should be exactly 12 characters, it's clear that you do, in fact, need a check constraint. The issue in your original snippet was a misplacement of the closing bracket. The following snippet shows the correct placement, with the entire condition enclosed in the check clause's brackets:

create table customer (
    rollcall number CONSTRAINT rc_pk PRIMARY KEY,
    fname varchar(2) not NULL,
    lname varchar(2)  not NULL,
    phone varchar2(12) check (length(phone) = 12) -- here
);
Mureinik
  • 297,002
  • 52
  • 306
  • 350