How to rectify “missing right parenthesis” problem?

Following is a sample of query I am working on right now:

CREATE TABLE book (
    book_id       NUMBER(6) NOT NULL CHECK(book_id > 0),
    isbn          VARCHAR2(20) NOT NULL UNIQUE,
    title         VARCHAR2(100) NOT NULL,
    shelf_letter  VARCHAR2(2) NOT NULL CHECK(shelf_letter IN('A-Z')),
    call_number   NUMBER(3) NOT NULL CHECK(call_number IN (1-999)),
    no_of_pages   NUMBER(4),
    no_of_copies  NUMBER(3) NOT NULL CHECK(no_of_copies >= 1),
    date_arrived  DATE NOT NULL DEFAULT SYSDATE,
    publisher_id  NUMBER(4) NOT NULL,
    cat_id        NUMBER(2) NOT NULL,
    CONSTRAINT shelf_call UNIQUE(shelf_letter,call_number) 
);

On compiling, it is throwing a “missing right parenthesis” error. I checked, but all the parentheses are accounted for. Can somebody help me?

Answer

The actual error you are seeing is caused by the date_arrived column with NOT NULL being placed before the default value of SYSDATE, when it should be after:

date_arrived  DATE DEFAULT SYSDATE NOT NULL,

In addition, the second and third check constraints, while technically valid syntax, look dubious:

shelf_letter  VARCHAR2(2) NOT NULL CHECK(shelf_letter IN('A-Z')),
call_number   NUMBER(3) NOT NULL CHECK(call_number IN (1-999)),

For the check on shelf_letter, presumably you want to enforce that it can only be the capital letters from A to Z. If so, then use REGEXP_LIKE:

shelf_letter  VARCHAR2(2) NOT NULL CHECK(REGEXP_LIKE(shelf_letter, '^[A-Z]$')),

For the check on call_number, use a range comparison:

call_number   NUMBER(3) NOT NULL CHECK(call_number BETWEEN 1 AND 999),

Putting all this together, use the following create table statement:

CREATE TABLE book (
    book_id       NUMBER(6) NOT NULL CHECK(book_id > 0),
    isbn          VARCHAR2(20) NOT NULL,
    title         VARCHAR2(100) NOT NULL,
    shelf_letter  VARCHAR2(2) NOT NULL CHECK(REGEXP_LIKE(shelf_letter, '^[A-Z]$')),
    call_number   NUMBER(6) NOT NULL CHECK(call_number BETWEEN 1 AND 999),
    no_of_pages   NUMBER(4),
    no_of_copies  NUMBER(3) NOT NULL CHECK(no_of_copies >= 1),
    date_arrived  DATE DEFAULT SYSDATE NOT NULL,
    publisher_id  NUMBER(4) NOT NULL,
    cat_id        NUMBER(2) NOT NULL,
    CONSTRAINT shelf_call UNIQUE(shelf_letter, call_number)
);

Demo