CODEWITHSUNDEEP
sqloracle-databasesyntax-errorddlcreate-table
me.user
me.user

Reputation: 35

Missing right parenthesis?

CREATE TABLE PERSON
(
    PID Int Not Null,
    FirstName Varchar2(15) Not Null,
    LastName Varchar2(15) Not Null,
    Email Varchar2(50) Not Null,
    Type Varchar2(15) Not Null Default Customer,
    Primary Key(PID)
);

I`m getting the following error:

ORA-00907: missing right parenthesis

Upvotes: 2

Views: 6292

Answers (1)

Mureinik
Mureinik

Reputation: 311393

type is a reserved word in SQL (or at least, in Oracle's flavor of it). You could either escape it using double quotes ("):

CREATE TABLE PERSON(
    PID Int Not Null,
    FirstName Varchar2(15) Not Null,
    LastName Varchar2(15) Not Null,
    Email Varchar2(50) Not Null,
    "Type" Varchar2(15) Default 'Customer' Not Null,
    Primary Key(PID)
);

Or just use a name that isn't a reserved word, such as person_type:

CREATE TABLE PERSON(
    PID Int Not Null,
    FirstName Varchar2(15) Not Null,
    LastName Varchar2(15) Not Null,
    Email Varchar2(50) Not Null,
    Person_Type Varchar2(15)  Default 'Customer' Not Null,
    Primary Key(PID)
);

EDIT:
As @a_horse_with_no_name commented, the default value "Customer" is a string literal, so it has to be enclosed with single quotes (').

EDIT2:
The default value clause should come before the not null clause.

Upvotes: 5

Related Questions