SQL error message: ORA-00907: missing right parenthesis-CodePudding

CREATE TABLE visitors 
(
    V_ID NUMBER(6) 
        CONSTRAINT pk_visitors_V_ID PRIMARY KEY, 
    V_FN VARCHAR2(15) NOT NULL, 
    V_LN VARCHAR2(15) NOT NULL,
    V_Contact NUMBER(10) NOT NULL,
    V_Address CHAR(10) NOT NULL,
    DoB DATE NOT NULL,
    Covid_Vaccine NUMBER(1) 
        CONSTRAINT ck_visitors_Covid_Vaccine CHECK (Covid_Vaccine IN ('1','2','3'))
    R_ID NUMBER(4) 
        CONSTRAINT fk_residents_R_IDREFERENCES residents(R_ID), 
    Date DATE NOT NULL
);

I'm trying to create this table on Apex Oracle for SQL but I keep getting this error:

ORA-00907: missing right parenthesis.

CodePudding user response：

That error is for missing comma after Covid_Vaccine column declaration. But after correcting this you will get invalid identifier error since date is reserve word . So I have changed the column name to ColDate. You will face another error ORA-02253: constraint specification not allowed here for not putting reference key word in R_ID column declaration. Please try below create table statement (it's working now).

 create table residents(R_ID NUMBER(4) PRIMARY KEY);

 CREATE TABLE visitors (
  V_ID NUMBER(6) CONSTRAINT pk_visitors_V_ID PRIMARY KEY, 
  V_FN VARCHAR2(15) NOT NULL, 
  V_LN VARCHAR2(15) NOT NULL,
  V_Contact NUMBER(10) NOT NULL,
  V_Address CHAR(10) NOT NULL,
  DoB DATE NOT NULL,
  Covid_Vaccine NUMBER(1) CONSTRAINT ck_visitors_Covid_Vaccine CHECK (Covid_Vaccine IN ('1','2','3')),
  R_ID NUMBER(4) CONSTRAINT fk_residents_R_IDREFERENCES references residents(R_ID), 
  ColDate DATE NOT NULL
 );

db<>fiddle here

CodePudding user response：

Various errors (missing comma, invalid column name (date is reserved for datatype), missing references keyword). When fixed:

SQL> CREATE TABLE visitors (
  2      v_id          NUMBER(6)    CONSTRAINT pk_visitors_v_id PRIMARY KEY,
  3      v_fn          VARCHAR2(15) NOT NULL,
  4      v_ln          VARCHAR2(15) NOT NULL,
  5      v_contact     NUMBER(10)   NOT NULL,
  6      v_address     CHAR(10)     NOT NULL,
  7      dob           DATE         NOT NULL,
  8      covid_vaccine NUMBER(1)    CONSTRAINT ck_visitors_covid_vaccine CHECK
  9                                   ( covid_vaccine IN ( '1', '2', '3' ) ),
 10      r_id          NUMBER(4)    CONSTRAINT fk_residents_r_idreferences
 11                                   REFERENCES residents(r_id),
 12      c_date        DATE         NOT NULL );

Table created.

SQL>