CODEWITHSUNDEEP
oracle-database
DaveDavidson
DaveDavidson

Reputation: 206

Oracle table creation missing right parenthesis

This should be a pretty simple problem but I just can't see what is wrong. I am trying to create a table with a few check and not null constraints on said table, however I am getting a missing right parenthesis error when trying to execute the command.

CREATE TABLE students (
     studentID NUMBER(5) PRIMARY KEY, 
    forename VARCHAR2(15) NOT NULL, 
    surname VARCHAR2(15) NOT NULL,
    street VARCHAR2(20),
    city VARCHAR2(15),
    postcode VARCHAR2(10) NOT NULL,
    dateOfBirth DATE() NOT NULL CHECK(dateOfBirth BETWEEN DATE '1999-01-01' AND SYSDATE), 
    gender VARCHAR2(10) CHECK(gender = 'male' OR gender = 'female' OR gender = 'both'),
    category VARCHAR2(15) NOT NULL CHECK(category = 'first year undergraduate' OR caregory = 'postgraduate'),
    fulltimeStudent VARCHAR(5) NOT NULL CHECK(fulltimeStudent = 'yes' OR fulltimeStudent = 'no'),
    nationality VARCHAR(25) NOT NULL,
    smoker VARCHAR(5) CHECK(smoker = 'yes' OR smoker = 'no') AND NOT NULL,
    specialNeeds VARCHAR(30),
    additionalComments VARCHAR(50),
    status VARCHAR(15) NOT NULL CHECK(status = 'placed' or status = 'waiting')
    );

Full error below - enter image description here

Upvotes: 0

Views: 53

Answers (1)

Francisco Sitja
Francisco Sitja

Reputation: 1003

There's no need for parenthesis at "DATE()". Also, you cannot use SYSDATE in check constraints, you may wish to use a trigger instead, and there's a typo at "CAREGORY".

You can run it like this:

CREATE TABLE students (
     studentID NUMBER(5) PRIMARY KEY, 
    forename VARCHAR2(15) NOT NULL, 
    surname VARCHAR2(15) NOT NULL,
    street VARCHAR2(20),
    city VARCHAR2(15),
    postcode VARCHAR2(10) NOT NULL,
    dateOfBirth DATE NOT NULL, 
    gender VARCHAR2(10) CHECK(gender = 'male' OR gender = 'female' OR gender = 'both'),
    category VARCHAR2(15) NOT NULL CHECK(category = 'first year undergraduate' OR category = 'postgraduate'),
    fulltimeStudent VARCHAR(5) NOT NULL CHECK(fulltimeStudent = 'yes' OR fulltimeStudent = 'no'),
    nationality VARCHAR(25) NOT NULL,
    smoker VARCHAR(5) NOT NULL CHECK(smoker = 'yes' OR smoker = 'no'),
    specialNeeds VARCHAR(30),
    additionalComments VARCHAR(50),
    status VARCHAR(15) NOT NULL CHECK(status = 'placed' or status = 'waiting')
    );

Edit-Added docs reference on sysdate in check constraints: https://docs.oracle.com/cd/B28359_01/server.111/b28286/clauses002.htm

Conditions of check constraints cannot contain the following constructs:

Subqueries and scalar subquery expressions

Calls to the functions that are not deterministic (CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV)

Calls to user-defined functions

Dereferencing of REF columns (for example, using the DEREF function)

Nested table columns or attributes

The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM

Date constants that are not fully specified

Upvotes: 2

Related Questions