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, Mar 28 '16 at 01:08
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, Mar 28 '16 at 01:16
`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, Mar 28 '16 at 01:19

score 3 · Answer 1 · answered Mar 28 '16 at 01:13

3

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

answered Mar 28 '16 at 01:13

MT0

143,790
11
59
117

Thanks. now I'm pretty sure this is the problem. I'll fix it. – Bebe Mar 29 '16 at 12:07
As I said, `IDENTITY` columns were only introduced in Oracle 12c. If you are using Oracle 11 then you **cannot** use them and you will have to use a sequence. Remove the `GENERATED ALWAYS AS IDENTITY` and it will work then you can use `CREATE SEQUENCE artist__seq;` to create the sequence with default options and can use that sequence in your `INSERT` statements. – MT0 Mar 29 '16 at 14:23
switched to sql server and finished my assignment :-| – Bebe Apr 26 '16 at 14:26
@VasinYuriy "not solved for me" is not a constructive statement as it doesn't tell us what didn't work or what the error was; I suggest you [ask a new question](https://stackoverflow.com/questions/ask) and include a [MRE] with full details of your problem including a minimal example of your code, the errors it produces and a description of what you are trying to achieve. – MT0 Nov 20 '19 at 15:02
@MT0, in differrent versions of DB there ara different ways to define primary key. I fount solution in this post: https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle – Vasin Yuriy Nov 21 '19 at 06:25

sql error ora-00907 missing right parenthesis create table

1 Answers1