Why is this CREATE command not running?-CodePudding

This is a part of a script which the whole script runs without errors. I followed the script and made minor changes, just by changing names, etc and discarded few commands like 'create or replace force view', sequences.

CREATE TABLE  "EMPLOYEES"
   ("EMPLOYEE_ID" NUMBER(6,0),
    "FIRST_NAME" VARCHAR2(20),
    "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
    "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
    "PHONE_NUMBER" VARCHAR2(20),
    "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
    "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
    "SALARY" NUMBER(8,2),
    "COMMISSION_PCT" NUMBER(2,2),
    "MANAGER_ID" NUMBER(6,0),
    "DEPARTMENT_ID" NUMBER(4,0),
        "BONUS" VARCHAR2(5),
     CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
     CONSTRAINT "EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX  ENABLE,
     CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
  USING INDEX  ENABLE
  );

This is one of the 'CREATE TABLE' commands that I edited from there.

CREATE TABLE  "PRODUCT"
   ("Prod_ID" VARCHAR2(10) CONSTRAINT "Prod_ID_NN" NOT NULL ENABLE,
    "Prod_Price" NUMBER(3,2) CONSTRAINT "Prod_Price_NN" NOT NULL ENABLE,
    "Prod_Avail" VARCHAR2(20) CONSTRAINT "Prod_Price_NN" NOT NULL ENABLE,
    "Prod_MDate" DATE CONSTRAINT "Prod_MDate_NN" NOT NULL ENABLE,
    "Prod_EDate" DATE CONSTRAINT "Prod_EDate_NN" NOT NULL ENABLE,
    "Cust_ID" NUMBER(4,0),
    "Sto_ID" VARCHAR(8) CONSTRAINT "Sto_ID_NN" NOT NULL ENABLE,
    "Sup_ID" VARCHAR(10),
    CONSTRAINT "Prod_ID_PK" PRIMARY KEY ("Prod_ID")
    CONSTRAINT "Prod_Date_Interval" CHECK (Prod_EDate > Prod_MDate) ENABLE,
    USING INDEX  ENABLE
   );

Errors came out; ORA-00907: missing right parenthesis

ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210200", line 673

ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658

ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210200", line 659

ORA-06512: at "APEX_210200.WWV_FLOW_DYNAMIC_EXEC", line 1829

"Prod_Avail" VARCHAR2(20) CONSTRAINT "Prod_Price_NN" NOT NULL ENABLE, "Prod_MDate" DATE CONSTRAINT "Prod_MDate_NN" NOT NULL ENABLE, "Prod_EDate" DATE CONSTRAINT "Prod_EDate_NN" NOT NULL ENABLE,

"Cust_ID" NUMBER(4,0),

"Sto_ID" VARCHAR(8) CONSTRAINT "Sto_ID_NN" NOT NULL ENABLE,

CodePudding user response：

I'd suggest you to forget about double quotes while working with Oracle. Nothing good in them, only problems.

When you fix errors

enclosing column names with mixed case into double quotes in constraints
use unique constraint names
rearrange using index enable

then it works:

SQL> CREATE TABLE "PRODUCT"
  2  (
  3     "Prod_ID"      VARCHAR2 (10) CONSTRAINT "Prod_ID_NN" NOT NULL ENABLE,
  4     "Prod_Price"   NUMBER (3, 2) CONSTRAINT "Prod_Price_NN" NOT NULL ENABLE,
  5     "Prod_Avail"   VARCHAR2 (20) CONSTRAINT "Prod_Avail_NN" NOT NULL ENABLE,
  6     "Prod_MDate"   DATE CONSTRAINT "Prod_MDate_NN" NOT NULL ENABLE,
  7     "Prod_EDate"   DATE CONSTRAINT "Prod_EDate_NN" NOT NULL ENABLE,
  8     "Cust_ID"      NUMBER (4, 0),
  9     "Sto_ID"       VARCHAR (8) CONSTRAINT "Sto_ID_NN" NOT NULL ENABLE,
 10     "Sup_ID"       VARCHAR (10),
 11     CONSTRAINT "Prod_ID_PK" PRIMARY KEY ("Prod_ID") USING INDEX ENABLE,
 12     CONSTRAINT "Prod_Date_Interval" CHECK ("Prod_EDate" > "Prod_MDate") ENABLE
 13  );

Table created.

SQL>

But - once again - I wouldn't do it that way. How would I do it? Lke this:

SQL> CREATE TABLE product
  2  (
  3     prod_id      VARCHAR2 (10) CONSTRAINT prod_id_nn NOT NULL ENABLE,
  4     prod_price   NUMBER (3, 2) CONSTRAINT prod_price_nn NOT NULL ENABLE,
  5     prod_avail   VARCHAR2 (20) CONSTRAINT prod_avail_nn NOT NULL ENABLE,
  6     prod_mdate   DATE CONSTRAINT prod_mdate_nn NOT NULL ENABLE,
  7     prod_edate   DATE CONSTRAINT prod_edate_nn NOT NULL ENABLE,
  8     cust_id      NUMBER (4, 0),
  9     sto_id       VARCHAR (8) CONSTRAINT sto_id_nn NOT NULL ENABLE,
 10     sup_id       VARCHAR (10),
 11     CONSTRAINT prod_id_pk PRIMARY KEY (prod_id) USING INDEX ENABLE,
 12     CONSTRAINT prod_date_interval CHECK (prod_edate > prod_mdate) ENABLE
 13  );

Table created.

SQL>