wisemen wisemen - 3 months ago 6
SQL Question

SQL Error: ORA-00904: "CATEGORYID": invalid identifier"

CREATE TABLE CUSTOMER1
(
CUSTOMERID VARCHAR2(2) NOT NULL,
FIRSTNAME VARCHAR2(50) NOT NULL,
LASTNAME VARCHAR2(50) NOT NULL,
PHONENUMBER VARCHAR2(50) NOT NULL,
BIRTHDATE DATE NOT NULL,
DRIVERLICENSENUMBER NUMBER,
STATUS VARCHAR2(50),
CREDITCARENUMBER NUMBER,
CONSTRAINT CustomerID_PK PRIMARY KEY(CUSTOMERID)
);



CREATE TABLE CCATEGORY
(
CATEGORYID INT NOT NULL,
CATEGORYNAME VARCHAR2(50) NOT NULL,
CONSTRAINT categoryID_pk PRIMARY KEY (CATEGORYID)
);


CREATE TABLE TAPE
(
TAPEID NUMBER(5) NOT NULL,
TAPETITLE VARCHAR2(200) NOT NULL,
RELEASEYEAR NUMBER(5) NOT NULL,
DATEPURCHASED DATE NOT NULL,
PRICE NUMERIC(5) NOT NULL,
RENTEDOUT VARCHAR2(20) NOT NULL,
RATING VARCHAR2(10) NOT NULL,
ACTIONONRETURN VARCHAR2(50) NOT NULL,
PRIMARY KEY(TAPEID),
CONSTRAINT FK_CATEGORYID FOREIGN KEY(CATEGORYID) REFERENCES CCATEGORY(CATEGORYID),
CONSTRAINT FK_RESERVEDBY FOREIGN KEY(CUSTOMERID) REFERENCES CUSTOMER(CUSTOMERID)
);


Customer, and CCategory table are created. When I try to create TAPE table I get the following error:
SQL Error: ORA-00904: "CATEGORYID": invalid identifier
. Can't seem to figure out why. I created the customer table first, then the category table before trying to create the TAPE table.

Answer

Problem is that you have not defined CATEGORYID and CUSTOMERID in TAPE table. Add it and it would work.

Complete statement would be

CREATE TABLE TAPE
 (
TAPEID         NUMBER(5) NOT NULL,
TAPETITLE      VARCHAR2(200) NOT NULL,
RELEASEYEAR    NUMBER(5) NOT NULL,
DATEPURCHASED  DATE NOT NULL,
PRICE          NUMERIC(5) NOT NULL,
RENTEDOUT      VARCHAR2(20) NOT NULL,
RATING         VARCHAR2(10) NOT NULL,
ACTIONONRETURN VARCHAR2(50) NOT NULL,
CATEGORYID   INT NOT NULL,
CUSTOMERID          VARCHAR2(2) NOT NULL,
CONSTRAINT tape_primary_key PRIMARY KEY(TAPEID), 
CONSTRAINT FK_CATEGORYID FOREIGN KEY(CATEGORYID) REFERENCES CCATEGORY(CATEGORYID),
CONSTRAINT FK_RESERVEDBY FOREIGN KEY(CUSTOMERID) REFERENCES CUSTOMER1(CUSTOMERID)
);