CODEWITHSUNDEEP
sqloracle-databasecreate-table
Erez Avior
Erez Avior

Reputation: 15

Oracle SQL Create Table Says Missing Parenthesis? Can't see it

I'm trying to create a certain table but I get this error from Oracle:

ORA-00907: missing right parenthesis

I tried to look it up but found nothing similar...

This is what I'm trying to do:

CREATE TABLE Employees
(
    EMPLOYEE_ID NUMBER NOT NULL GENERATED BY DEFAULT AS IDENTITY, 
    NAME VERCHAR2(30) NOT NULL, 
    PHONE_NUMBER VARCHAR(12) CHECK(PHONE_NUMBER LIKE '%%%-%%%-%%%%') NOT NULL, 
    SALARY NUMBER NOT NULL, 
    SENIORITY NUMBER NOT NULL, 
    PRIMARY KEY(EMPLOYEE_ID)
);

Upvotes: 0

Views: 238

Answers (1)

Nick
Nick

Reputation: 147286

You have a few problems:

  1. As @GordonLinoff pointed out in the comments, VERCHAR2 is a typo and should be changed to VARCHAR2;
  2. As @TimBiegeleisen pointed out in his (now deleted) answer, your CHECK constraint allows for any value that has two hyphen's in it (e.g. x-y-z) to be inserted into the phone number. You should use something like what he suggested i.e.
    REGEXP_LIKE(PHONE_NUMBER, '\d{3}-\d{3}-\d{4}')
  3. Finally, and the actual reason for the error, the NOT NULL declaration on EMPLOYEE_ID should be after the GENERATED clause, not before it. Note you might actually want
    GENERATED BY DEFAULT ON NULL AS IDENTITY
    to insert an identity value if NULL is inserted (instead of generating an error as your current definition will)

Your query should be:

create table Employees
(
EMPLOYEE_ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL, 
NAME VARCHAR2(30) NOT NULL, 
PHONE_NUMBER VARCHAR(12) CHECK(REGEXP_LIKE(PHONE_NUMBER, '\d{3}-\d{3}-\d{4}')) NOT NULL, 
SALARY NUMBER NOT NULL, 
SENIORITY NUMBER NOT NULL, 
PRIMARY KEY(EMPLOYEE_ID)
);

Here's a demo on dbfiddle which also shows the effect of using GENERATED BY DEFAULT ON NULL AS IDENTITY instead of GENERATED BY DEFAULT AS IDENTITY NOT NULL.

Upvotes: 2

Related Questions