I'm getting "Invalid Identifier Error" for one of my Oracle Apex Queries

Question

I'm trying to run a script on Oracle Apex and so far all the tables and queries work except the last one. It returns the error "ORA-00904: : invalid identifier ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_200200", line 626 ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_200200", line 612 ORA-06512: at "APEX_200200.WWV_FLOW_DYNAMIC_EXEC", line 1749." What should I do to fix this error?

CREATE TABLE customer(
     VIN            VARCHAR2(17)
                    CONSTRAINT vehicles__VIN__fk
                      REFERENCES vehicles (VIN) ON DELETE SET NULL,
     sale_date      DATE
                    CONSTRAINT sales__sale_date__fk 
                      REFERENCES sales (sale_date) ON DELETE SET NULL,
     c_name         VARCHAR2(50)
                    CONSTRAINT sales__c_name__nn NOT NULL,
     address        VARCHAR2(50)
                    CONSTRAINT sales__address__nn NOT NULL,
     phone          VARCHAR2(11)
                    CONSTRAINT sales__phone__nn NOT NULL,
     gender         VARCHAR2(6)
                    CONSTRAINT sales__gender__nn NOT NULL,
     a_income       VARCHAR2(30)
                    CONSTRAINT sales__a_income__nn NOT NULL,
    );

I don't know if it helps but VIN and sale_date reference these two working queries:

CREATE TABLE vehicles(
  VIN   VARCHAR2(17)
        CONSTRAINT vehicles__VIN__pk PRIMARY KEY,
  brand VARCHAR2(20)
        CONSTRAINT vehicles__brand__nn NOT NULL,
  model VARCHAR2(20)
        CONSTRAINT vehicles__model__nn NOT NULL,
  color VARCHAR2(10)
        CONSTRAINT vehicles__color__nn NOT NULL
);

CREATE TABLE sales(
     sale_date      DATE, 
     price          VARCHAR2(30)
                    CONSTRAINT sales__price__nn NOT NULL,
     VIN            VARCHAR2(17)
                    CONSTRAINT vehicles__VIN__fk
                        REFERENCES vehicles (VIN) ON DELETE SET NULL,
     d_id           VARCHAR2(10)
                     CONSTRAINT dealer__d_id__fk 
                      REFERENCES dealer (d_id) ON DELETE SET NULL,
    CONSTRAINT sales__sale_date__pk PRIMARY KEY (sale_date)
    );

You expect us to tell you what is wrong with a query when you don't show us the query? Can _you_ debug what you cannot see? As for your design - why is GENDER 6 chars when you only need 1 (m/f). Why is a_income a varchar when it should be a number? There is really nothing gained by naming Not Null constraints. very uncommon to do so. — EdStevens, Oct 10 '20 at 23:38

score 1 · Accepted Answer · answered Oct 11 '20 at 00:31

Remove the last comma.

Also, if your constraints have the naming convention <tablename>__<columnname>__<constrainttype> then don't just copy/paste from another table and update the column name; you need to update the table name as well or you will find you have duplicate constraint names which will raise an exception.

CREATE TABLE customer(
     VIN            VARCHAR2(17)
                    CONSTRAINT customer__VIN__fk
                      REFERENCES vehicles (VIN) ON DELETE SET NULL,
     sale_date      DATE
                    CONSTRAINT customer__sale_date__fk 
                      REFERENCES sales (sale_date) ON DELETE SET NULL,
     c_name         VARCHAR2(50)
                    CONSTRAINT customer__c_name__nn NOT NULL,
     address        VARCHAR2(500)
                    CONSTRAINT customer__address__nn NOT NULL,
     phone          VARCHAR2(11)
                    CONSTRAINT customer__phone__nn NOT NULL,
     gender         CHAR(1)
                    CONSTRAINT customer__gender__nn NOT NULL
                    CONSTRAINT customer__gender__chk
                      CHECK ( gender IN ( 'M', 'F' /*, 'A', 'B', 'C'*/ ) ),
     a_income       NUMBER(12,2)
                    CONSTRAINT customer__a_income__nn NOT NULL
);

Then comes the other questions:

Why does a customer have a VIN (vehicle identification number)? A customer is not limited to owning a single car.
Why does a customer have a sale_date? If you are referring to a car sale then why is the customer limited to a single sale? You probably want to fix both these two by moving the data to a separate table called customer_cars (or something similar) so that each customer can own multiple cars and each car can be owned by multiple customers (at different times).
Do you expect all customers' addresses to fit in 50 characters?
Why is gender a VARCHAR(6) and not a CHAR(1) with values M/F (extend with additional character codes as appropriate)?
Why is a_income a string and not a NUMBER?

Surely `varchar2(1)` is a better choice of datatype than `char(1)`? [(Pet peeve alert.)](https://stackoverflow.com/a/42165653/230471) Anyway, maybe the OP wants to include non-binary genders. — William Robertson, Oct 11 '20 at 10:32
@WilliamRobertson There is a semantic difference. `CHAR` is that you want a fixed-length string; `VARCHAR2` is that you want a variable-length string. In this case the data is a fixed-length string so semantically a `CHAR` is appropriate. However, if you are used to people miss-using `CHAR` and want a simple rule to say "just use `VARCHAR2` for everything" then that is fine. — MT0, Oct 11 '20 at 11:17
As for non-binary genders, that is covered in my answer when I say "(extend with additional character codes as appropriate)" and have commented out potential additional check conditions in the code. — MT0, Oct 11 '20 at 11:19

I'm getting "Invalid Identifier Error" for one of my Oracle Apex Queries

1 Answers1