sql error ora-00907 missing right parenthesis create table

Question

I'm creating a table like this,

CREATE TABLE ARTIST (

    ArtistID            Int             NOT NULL IDENTITY(1,1),
    LastName            Char(25)        NOT NULL,
    FirstName           Char(25)        NOT NULL, 
    Nationality         Char(30)        NULL,
    DateOfBirth         Numeric(4)      NULL, 
    DateDeceased        Numeric(4)      NULL,
    CONSTRAINT  ArtistPK            PRIMARY KEY(ArtistID),
    CONSTRAINT  ArtistAK1           UNIQUE(LastName, FirstName),    
    CONSTRAINT  BirthValuesCheck    CHECK (DateOfBirth < DateDeceased),
    CONSTRAINT  ValidBirthYear      CHECK
            (DateOfBirth LIKE '[1-2][0-9][0-9][0-9]'),
    CONSTRAINT  ValidDeathYear      CHECK
            (DateDeceased LIKE '[1-2][0-9][0-9][0-9]')
);

and I get this error :

Error report - SQL Error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

Any idea why?

It's not an amazing answer but have you tried removing the constraints and then adding them one by one to see which is failing? — Tim Ogilvy
Why do you have a UNIQUE constraint on first & last name? Is it not possible to have multiple artists with the same name? Also, why not use a DATE datetype for Date of Birth/Death? — MT0
char(25) and char(30) data types are almost certainly a poor choice. names and nationalities are variable length strings, use varchar2 instead. Your DateOfBirth and DateDeceased should almost certainly be a date rather than just storing a numeric year. If you want to put a check constraint on a numeric field, make a numeric comparison (i.e. YearOfBirth BETWEEN 1000 and 2999) — Justin Cave

MT0 MT0 · Accepted Answer · 2016-03-28T01:13:20

The Oracle 12c syntax for identity columns is:

GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

If you are using Oracle 11 or below then you cannot have IDENTITY columns and will have to use a sequence but, assuming you are using 12c then you want:

CREATE TABLE ARTIST (
    ArtistID            Int             NOT NULL GENERATED ALWAYS AS IDENTITY,
    LastName            Char(25)        NOT NULL,
    FirstName           Char(25)        NOT NULL, 
    Nationality         Char(30)        NULL,
    DateOfBirth         Numeric(4)      NULL, 
    DateDeceased        Numeric(4)      NULL,
    CONSTRAINT  ArtistPK            PRIMARY KEY(ArtistID),
    CONSTRAINT  ArtistAK1           UNIQUE(LastName, FirstName),    
    CONSTRAINT  BirthValuesCheck    CHECK (DateOfBirth < DateDeceased),
    CONSTRAINT  ValidBirthYear      CHECK
            (DateOfBirth LIKE '[1-2][0-9][0-9][0-9]'),
    CONSTRAINT  ValidDeathYear      CHECK
            (DateDeceased LIKE '[1-2][0-9][0-9][0-9]')
);

sql error ora-00907 missing right parenthesis create table

1 Answers