0

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)
);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Erez Avior
  • 15
  • 4

1 Answers1

2

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.

Nick
  • 138,499
  • 22
  • 57
  • 95
  • For some reason, I'm still getting the same error code – Erez Avior Jan 18 '21 at 13:17
  • @ErezAvior which version of Oracle are you using? – Nick Jan 18 '21 at 22:00
  • `IDENTITY` columns didn't exist before 12c. You might find this useful: https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle – Nick Jan 19 '21 at 10:47
  • Ok so I tried to do it like that: ``` create table Employees ( EMPLOYEE_ID NUMBER 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) ); ALTER TABLE Employees ADD ( CONSTRAINT dept_pk PRIMARY KEY (EMPLOYEE_ID)); CREATE SEQUENCE dept_seq START WITH 1; ``` And the error jumped: `ORA-00911: invalid character` – Erez Avior Jan 19 '21 at 22:39
  • You should probably ask a new question. That works fine in 11g (although you don't need the `ALTER TABLE` command as you already declare a primary key) but I don't have access to a 10g version to test it. https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=8f4823397f105e6dd8c8827d7c387be3 – Nick Jan 20 '21 at 01:57