Oracle SQL can't find the cause of the error

C. Greer Source

I keep getting an invalid identifier error on part of my code for creating tables. Every other part of my code executes fine but this one table does not.

CREATE TABLE saleinv
(saleinv VARCHAR(6)
,cname VARCHAR (20) NOT NULL
,salesman VARCHAR(20) NOT NULL
,saledate DATE
,serial VARCHAR(8) NOT NULL
,totalprice NUMERIC(9,2)
,discount NUMERIC(8,2)
,net NUMERIC(9,2)
,tax NUMERIC(8,2)
,licfee NUMERIC(6,2)
,comission NUMERIC(8,2)
,tradeserial VARCHAR(8) NOT NULL
,tradeallow NUMERIC(9,2)
,fire VARCHAR(1)
      CHECK (fire IN ('Y','N'))
,collision VARCHAR(1)
      CHECK (collision IN ('Y','N'))
,liability VARCHAR(1)
      CHECK (liability IN ('Y','N'))
,property VARCHAR(1)
      CHECK (property IN ('Y','N'))
,CONSTRAINT salesinv_saledate_nn NOT NULL
      CHECK (saledate > TO_DATE('01-JAN-1990','DD-MON-YYYY'))
,CONSTRAINT saleinv_pk PRIMARY KEY (saleinv)
,CONSTRAINT saleinv_cname_fk FOREIGN KEY (cname) REFERENCES customer (cname)
,CONSTRAINT saleinv_salesman_fk FOREIGN KEY (salesman) REFERENCES employee (empname)
,CONSTRAINT saleinv_serial_fk FOREIGN KEY (serial) REFERENCES car (serial)
,CONSTRAINT saleinv_tradeserial_fk FOREIGN KEY (tradeserial) REFERENCES car (serial)
)

This is the output:

Error report -
ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

If anyone could help me figure out why this error is happening would be greatly appreciated.

sqloracleoracle-sqldeveloper

Answers

answered 6 months ago Littlefoot #1

SQL*Plus is such a nice tool. Those who live by GUI, die by GUI.

Scroll down; then remove "NOT NULL" from line 23 and put it into line 5.

SQL> CREATE TABLE saleinv
  2  (saleinv VARCHAR(6)
  3  ,cname VARCHAR (20) NOT NULL
  4  ,salesman VARCHAR(20) NOT NULL
  5  ,saledate DATE
  6  ,serial VARCHAR(8) NOT NULL
  7  ,totalprice NUMERIC(9,2)
  8  ,discount NUMERIC(8,2)
  9  ,net NUMERIC(9,2)
 10  ,tax NUMERIC(8,2)
 11  ,licfee NUMERIC(6,2)
 12  ,comission NUMERIC(8,2)
 13  ,tradeserial VARCHAR(8) NOT NULL
 14  ,tradeallow NUMERIC(9,2)
 15  ,fire VARCHAR(1)
 16        CHECK (fire IN ('Y','N'))
 17  ,collision VARCHAR(1)
 18        CHECK (collision IN ('Y','N'))
 19  ,liability VARCHAR(1)
 20        CHECK (liability IN ('Y','N'))
 21  ,property VARCHAR(1)
 22        CHECK (property IN ('Y','N'))
 23  ,CONSTRAINT salesinv_saledate_nn NOT NULL
 24        CHECK (saledate > TO_DATE('01-JAN-1990','DD-MON-YYYY'))
 25  ,CONSTRAINT saleinv_pk PRIMARY KEY (saleinv)
 26  ,CONSTRAINT saleinv_cname_fk FOREIGN KEY (cname) REFERENCES customer (cname)
 27  ,CONSTRAINT saleinv_salesman_fk FOREIGN KEY (salesman) REFERENCES employee (empname)
 28  ,CONSTRAINT saleinv_serial_fk FOREIGN KEY (serial) REFERENCES car (serial)
 29  ,CONSTRAINT saleinv_tradeserial_fk FOREIGN KEY (tradeserial) REFERENCES car (serial)
 30  );
,CONSTRAINT salesinv_saledate_nn NOT NULL
                                 *
ERROR at line 23:
ORA-00904: : invalid identifier


SQL>

comments powered by Disqus