Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904: "COPY_ID": invalid identifier (Project Build 2.2.1.00.04)
ORA-00904: "COPY_ID": invalid identifier [message #280219] Mon, 12 November 2007 19:05 Go to next message
IIMarckus
Messages: 3
Registered: November 2007
Junior Member
Hi guys,

I'm a college student, and am taking "Intro to SQL." We've just moved into creating tables and entering data. We enter stuff in the Oracle Application Express, found at iacademy.oracle.com .

When entering data into the table MOVIE_RENTAL, I would receive an error, which turned out to be due to a foreign key referencing the wrong table. After a lot of muddling around with the GUI's constraint options, I finally saved the SQL for the table and decided to restart the table from scratch. Now, however, I get 'ORA-00904: "COPY_ID": invalid identifier' whenever I try to create it.

CREATE TABLE  "MOVIE_RENTAL" 
(
"EMPL_ID" NUMBER(5,0) NOT NULL ENABLE, 
"MEMB_ID" NUMBER(5,0) NOT NULL ENABLE, 
"MOVIE_ID" NUMBER(3,0) NOT NULL ENABLE, 
"COPY_NO" NUMBER(2,0) NOT NULL ENABLE, 
"CHKOUT_DATE" DATE NOT NULL ENABLE, 
"CHKIN_DATE" DATE NOT NULL ENABLE, 
CONSTRAINT "RENTAL_PK" PRIMARY KEY ("EMPL_ID", "MEMB_ID", "MOVIE_ID", "COPY_NO", "CHKOUT_DATE") ENABLE,
CONSTRAINT "RENTAL_FK1" FOREIGN KEY ("EMPL_ID") REFERENCES  "MOVIE_EMPLOYEES" ("EMPL_ID") ENABLE,
CONSTRAINT "RENTAL_FK2" FOREIGN KEY ("MEMB_ID")  REFERENCES  "MOVIE_CLUB_MEMBER" ("MEMB_ID") ENABLE,
CONSTRAINT "RENTAL_FK3" FOREIGN KEY ("MOVIE_ID") REFERENCES  "MOVIE" ("MOVIE_ID") ENABLE,
CONSTRAINT "RENTAL_FK4" FOREIGN KEY ("COPY_ID") REFERENCES "MOVIE_INVENTORY" ("COPY_ID") ENABLE
   )


COPY_ID is the same foreign key that was referencing an incorrect table before I remade the table; I'm unsure if that's related.

In case it's related, here's the source for the table that COPY_ID is supposed to reference:

CREATE TABLE  "MOVIE_INVENTORY" 
   (	"COPY_NO" NUMBER(2,0), 
	"DATE_RCVD" DATE NOT NULL ENABLE, 
	"MOVIE_ID" NUMBER(3,0), 
	 CONSTRAINT "MOV_INV_PK" PRIMARY KEY ("COPY_NO", "MOVIE_ID") ENABLE
   )
/
ALTER TABLE  "MOVIE_INVENTORY" ADD CONSTRAINT "MOV_INV_FK" FOREIGN KEY ("MOVIE_ID")
	  REFERENCES  "MOVIE" ("MOVIE_ID") ENABLE
/
This table works fine, but the first table gets errors when I try to run the code.
Re: ORA-00904: "COPY_ID": invalid identifier [message #280220 is a reply to message #280219] Mon, 12 November 2007 19:29 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>CONSTRAINT "RENTAL_FK4" FOREIGN KEY ("COPY_ID") REFERENCES "MOVIE_INVENTORY" ("COPY_ID") ENABLE
might succeed if changed to
CONSTRAINT "RENTAL_FK4" FOREIGN KEY ("COPY_ID") REFERENCES "MOVIE_INVENTORY" ("COPY_NO") ENABLE

then again, it might error again
Re: ORA-00904: "COPY_ID": invalid identifier [message #280222 is a reply to message #280219] Mon, 12 November 2007 19:36 Go to previous messageGo to next message
IIMarckus
Messages: 3
Registered: November 2007
Junior Member
Thanks, that was definitely part of the problem. It does give another error, though:

CREATE TABLE  "MOVIE_RENTAL" 
   (	"EMPL_ID" NUMBER(5,0) NOT NULL ENABLE, 
	"MEMB_ID" NUMBER(5,0) NOT NULL ENABLE, 
	"MOVIE_ID" NUMBER(3,0) NOT NULL ENABLE, 
	"COPY_NO" NUMBER(2,0) NOT NULL ENABLE, 
	"CHKOUT_DATE" DATE NOT NULL ENABLE, 
	"CHKIN_DATE" DATE NOT NULL ENABLE, 
	 CONSTRAINT "RENTAL_PK" PRIMARY KEY ("EMPL_ID", "MEMB_ID", "MOVIE_ID", "COPY_NO", "CHKOUT_DATE") ENABLE,
CONSTRAINT "RENTAL_FK1" FOREIGN KEY ("EMPL_ID") REFERENCES  "MOVIE_EMPLOYEES" ("EMPL_ID") ENABLE,
CONSTRAINT "RENTAL_FK2" FOREIGN KEY ("MEMB_ID")  REFERENCES  "MOVIE_CLUB_MEMBER" ("MEMB_ID") ENABLE,
CONSTRAINT "RENTAL_FK3" FOREIGN KEY ("MOVIE_ID") REFERENCES  "MOVIE" ("MOVIE_ID") ENABLE,
CONSTRAINT "RENTAL_FK4" FOREIGN KEY ("COPY_NO") REFERENCES "MOVIE_INVENTORY" ("COPY_NO") ENABLE
   )


"ORA-02270: no matching unique or primary key for this column-list"

Any ideas?
Re: ORA-00904: "COPY_ID": invalid identifier [message #280224 is a reply to message #280219] Mon, 12 November 2007 19:38 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
CONSTRAINT "RENTAL_FK4" FOREIGN KEY ("COPY_NO") REFERENCES "MOVIE_INVENTORY" ("COPY_NO", "MOVIE_ID") ENABLE
Re: ORA-00904: "COPY_ID": invalid identifier [message #280808 is a reply to message #280219] Wed, 14 November 2007 17:52 Go to previous message
IIMarckus
Messages: 3
Registered: November 2007
Junior Member
It turns out that the problem was that COPY_NO wasn't actually a unique key. This was fixed by setting MOVIE_INVENTORY's primary key to be a composite of COPY_NO and MOVIE_ID, then replacing the third and fourth foreign keys in MOVIE_RENTAL with:
ALTER TABLE  "MOVIE_RENTAL" ADD CONSTRAINT "RENTAL_FK3" FOREIGN KEY ("COPY_NO", "MOVIE_ID")
	  REFERENCES  "MOVIE_INVENTORY" ("COPY_NO", "MOVIE_ID") ENABLE
/
Previous Topic: Help With Strings and Quotes
Next Topic: Hierarchy Builder SQL (merged 2 cross-posts)
Goto Forum:
  


Current Time: Thu Dec 08 18:31:05 CST 2016

Total time taken to generate the page: 0.09169 seconds