Re: R: Oracle 8i lite. Problems with Foreign Key on TIME and DATE type columns

Franz wrote:

> you are right, but in my db the names are italian (Data and Ora), I
> translated them in english to make them easier to understand on here...
> Anyway i solved that problem, but i get errors when querying into java code,
> using jdbc...
> errors like invalid column name, or column not found... always referring to
> DATE and TIME columns, while the same querys are ok if typed directly into
> sqlplus
>
> Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message
> 3EE8C6BB.1B4D352A_at_exxesolutions.com...
> > Franz wrote:
> >
> > > Is there any problem with foreign key referencing TIME or DATE type
> columns?
> > >
> > > I'm using oracle 8i lite, for a university project, and I have a problem
> > > with a single foreign key.
> > >
> > > I receive this error message:
> > >
> > > OCA-30035: column not found
> > > [POL-5205] column not found
> > >
> > > When I try to set the foreign key
> > >
> > > ALTER TABLE booking ADD
> > > (CONSTRAINT fk_booking
> > > FOREIGN KEY (date, time, room)
> > > REFERENCES show(date, time, room));
> > >
> > > between these tables
> > >
> > > CREATE TABLE booking(
> > > id_customer VARCHAR(6),
> > > date DATE,
> > > time TIME,
> > > room VARCHAR(20),
> > > PRIMARY KEY (id_customer, date, time, room)
> > > );
> > >
> > > CREATE TABLE show(
> > > date DATE,
> > > time TIME,
> > > room VARCHAR(20),
> > > movie VARCHAR(6),
> > > PRIMARY KEY (date, time, room)
> > > );
> > >
> > > Thanks
> >
> > I see a huge problem in what you are doing. Try this query:
> >
> > SELECT *
> > FROM v_$reserved_words;
> >
> > and study the results.
> >
> > Why woud you think you could name a column 'DATE' or 'TIME'?
> >
> > --
> > Daniel Morgan
> > http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> > damorgan_at_x.washington.edu
> > (replace 'x' with a 'u' to reply)
> >
> >

My Italian is barely good enough to get into trouble so I'll to not attempt any. But English, Italian, or Persian ... change the name of the columns. And if you are still having problems post the actual code and the full and complete error message from ORA- to the end ... don't paraphrase it.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)