Invalid identifier when trying to create a table with SQL

Question

I am trying to create a table and I get an invalid identifier error. What does this mean? I've looked over the code over and over. All of my other statements run perfectly. Can't figure out what the issue is with this.

Here is the statement that returns the error:

/* Create Transaction Table */
31 CREATE TABLE TRANSACTION(TxNbr INTEGER PRIMARY KEY,
32                        TxCode CHAR(1) NOT NULL,
33                        AccountNbr INTEGER NOT NULL,
34                        Amount DECIMAL(13,2) NOT NULL,
35                         Date DATE,
36                         Time TIME,
37                         RefNbr VARCHAR(3),
38                         FOREIGN KEY(AccountNbr) REFERENCES ACCOUNT (AccountNbr) ON DELETE SET NULL,
39                         FOREIGN KEY(TxCode) REFERENCES TX_TYPE (TxCode) ON DELETE SET NULL
40                         );

Here is the error:

Error starting at line : 31 in command -
Error report -
ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Answer 1

There are two problems with your code (not considering the foreign key constraints, that we cannot validate without actually knowing the structure of the related tables)

• DATE is a reserved word in Oracle (that's a datatype), so it can't be used for a column name, unless you surround it with double quotes - but if you do so, then you need to quote the column everytime you access it. I would suggest just using a column name that does not conflict with a language keyword.

• There is no TIME datatype in Oracle; the DATE datatype actually stores the date and time - which is generally better than separating the date and time portion in two different columns anyway anyway, since it makes date arithmetics easier. I would recommend just removing that column.

Consider:

CREATE TABLE TRANSACTION(
    TxNbr INTEGER PRIMARY KEY,
    TxCode CHAR(1) NOT NULL,
    AccountNbr INTEGER NOT NULL,
    Amount DECIMAL(13,2) NOT NULL,
    TxDate DATE,
    RefNbr VARCHAR(3)
    FOREIGN KEY(AccountNbr) REFERENCES ACCOUNT (AccountNbr) ON DELETE SET NULL,
    FOREIGN KEY(TxCode) REFERENCES TX_TYPE (TxCode) ON DELETE SET NULL
);