CODEWITHSUNDEEP
sqloracle-databaseora-00907
Bebe
Bebe

Reputation: 83

sql error ora-00907 missing right parenthesis create table

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?

Upvotes: 1

Views: 2981

Answers (1)

MT0
MT0

Reputation: 167822

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]')
);

Upvotes: 3

Related Questions