Error while creating a table in oracle 11g

Question

CREATE TABLE Prescription (
    pre_id NUMBER(10),
    pssn CHAR(11),
    phy_ssn CHAR(11),
    date CHAR(11),
    quantity INTEGER,
    trade name CHAR(20),
    pharm id CHAR(11), 
    drop_off_time CHAR(10), 
    pick_up_time CHAR(10), 
    status CHAR (15),
    PRIMARY KEY(pre_id),
    FOREIGN KEY (pssn) REFERENCES Pri_Phy_Patient,
    FOREIGN KEY (phy_ssn) REFERENCES Doctor,
    FOREIGN KEY (trade_name, pharm_id) REFERENCES Make_Drug); 

I get ORA-00904:invalid identifier error on using the above sql statement.

The other sql statements related to the above one are:

CREATE TABLE Doctor (
    phy_ssn CHAR(11),
    name CHAR(20), 
    speciality CHAR(30),
    exp_years CHAR(10),
    PRIMARY KEY (phy_ssn));

CREATE TABLE Pri_Phy_Patient (
    pssn CHAR(11),
    name CHAR(20),
    age INTEGER,
    address CHAR(20),
    phy ssn CHAR(11),
    PRIMARY KEY (pssn),
    FOREIGN KEY (phy_ssn) REFERENCES Doctor );

CREATE TABLE Make_Drug (
    trade_name CHAR(20),
    pharm_id CHAR(11),
    formula VARCHAR(100)
    PRIMARY KEY (trade_name, pharm_id),
    FOREIGN KEY (pharm_id) REFERENCES Pharm_co);

Could anyone suggest on what I can do about this?!!

Answer 1

date CHAR(11) Date is a reserved word in SQL. It cannot be declared as a column in the way you did. Please change the column name and it will work.