I'm having a problem try to create

[mrasad]

I'm having a problem try to create for keys in my oracle table.

Example;

create table loan
(LoanID varchar2(6) PRIMARY KEY,
Issue_Date date not null,
Due_Date date not null,
Return_Date date not null,
Fine_total number(3,2),
FOREIGN KEY (Item_ID) REFERENCES AV_Item,
FOREIGN KEY (BookNo) REFERENCES Book,
FOREIGN KEY (MemberID) REFERENCES Member);

I have a member, book and av_item table. but i get the following error message;

SQL> create table loan
2 (LoanID varchar2(6) PRIMARY KEY,
3 Issue_Date date not null,
4 Due_Date date not null,
5 Return_Date date not null,
6 Fine_total number(3,2),
7 FOREIGN KEY (Item_ID) REFERENCES AV_Item,
8 FOREIGN KEY (BookNo) REFERENCES Book,
9 FOREIGN KEY (MemberID) REFERENCES Member);
FOREIGN KEY (Item_ID) REFERENCES AV_Item,
*
ERROR at line 7:
ORA-00904: invalid column name

Its not only the AV_item, its placed the bookno before the av, and still get the same error message but this time on the book table.

?

 

[mrasad]

Sorry about the title. Having problems creating FOREIGN KEYS!

 

[SantaMufasa]

MrAsad,

Here is similar code using the syntax you need:

SQL> create table loan
  2   (LoanID varchar2(6) PRIMARY KEY,
  3   Issue_Date date not null,
  4   Due_Date date not null,
  5   Return_Date date not null,
  6   Fine_total number(3,2),
  7   BookNo  REFERENCES s_product(id),
  8   MemberID REFERENCES s_emp(id))
  9  /

Table created.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:53 (28Dec03) GMT, 12:53 (28Dec03) Mountain Time)

 

[mrasad]

thank you

SQL> create table loan
2 (LoanID varchar2(6) PRIMARY K
3 Issue_Date date not null,
4 Due_Date date not null,
5 Return_Date date not null,
6 Fine_Total number(3,2),
7 Item_ID REFERENCES AV_Item,
8 Bookno REFERENCES Book,
9 MemberID REFERENCES Member);

Table created.