ORA-00904: : invalid identifier while trying to create a table although no reserve keywords are used

Question

create table LPU_Employee (
    UID       decimal(6)  not null primary key,
    EName     varchar(30) not null,
    Hire_Date date        not null,
    Deparment varchar(30) null,
    HOD       varchar(30) null,
    HOD_UID   decimal(6)  not null,
    Salary    double      null
);

Answer 1

• UID is a reserved word. Use either a different column name or put it into double quotes
• double is not recognised (sorry, I spell it the British way) as a data type. Use float .

create table LPU_Employee (
    "UID"     decimal(6)  not null primary key,
    EName     varchar(30) not null,
    Hire_Date date        not null,
    Deparment varchar(30) null,
    HOD       varchar(30) null,
    HOD_UID   decimal(6)  not null,
    Salary    FLOAT      null
);

Answer 2

The answer to your direct question is about UID. I would strongly recommend renaming the column to avoid the conflict. But I have other suggestions as well:

create table LPU_Employee (
    employee_UID decimal(6) not null primary key,
    EName     varchar(30) not null,
    Hire_Date date        not null,
    Deparment varchar2(30) null,
    HOD       varchar2(30) null,
    HOD_UID   decimal(6)  not null,
    Salary    number      null
);

In particular, instead of varchar() this uses varchar2() -- which Oracle recommends for such types. In addition, salary does not use double; it uses number. In my experience, monetary amounts should never be stored using floating point representations.

In addition, I question the use of decimal(6) for the ids. In my experience with such ids leading zeros are used. So, instead of 123 the id would be 000123 -- and everyone's would be the same length.

If that is the case here, then use a string where the values must be digits:

employee_UID varchar2(6)  not null primary key check (regexp_like(employee_UID, '^[0-9]{6}$'))

If the 6 is arbitrary, then just use int or number. There is no need to predefine the length (beyond the storage class for the type).