Re: date column part of primary key in Oracle

[prev] [thread] [next] [Date index for 2005/02/10]

On Feb 10, 2005, at 9:14 AM, Hartmaier Alexander wrote:

> Yes please update the Wiki section on Oracle dates.

Done:
<http://www.class-dbi.com/cgi-bin/wiki/index.cgi?=20
WorkingWithOracleDateFields>

> But wouldn't it be easier and nicer to have a single unique key field =20=

> 'order_id'? Why can there be multiple rows with the same order_id in =20=

> your table?
>
> -Alex

The table in question stores user notes attached to a purchase =20
requisition. The req is identified by the order_id (so that's actually =20=

a foreign key), and there can be multiple notes. So we could have had =20=

an arbitrary sequence as the PK, but we were already storing date as =20
part of the record so we (the DBA actually) decided to use date.

> -----Urspr=FCngliche Nachricht-----
> Von: Jim Brandt [mailto:cbrandt@xxxxxxx.xxx]
> Gesendet: Dienstag, 8. Februar 2005 22:00
> An: cdbi-talk@xxxxxx.xxxxx.xxx
> Betreff: Re: date column part of primary key in Oracle
>
> Amazing how posting to the list sharpens the mind :)
>
> Yes, you can use dates in primary keys.
>
> What you can't do is use functions like TO_CHAR in a set_sql statement
> that is selecting the records you want to use. I had a select =
statement
> that was doing a TO_CHAR on my enter_date and apparently CDBI didn't
> like that when enter_date was in the primary key.
>
> Strange thing is that it worked if enter date was in Essential.
>
> Anyway, problem solved.
>
> Would this warrant a change to the docs or at least a mention on the
> Kwiki? I know I spent some time on it :)
>
> Jim
>
>
> On Feb 8, 2005, at 3:46 PM, Jim Brandt wrote:
>
>>
>> Is there something in CDBI that prevents you from having a date
>> datatype as part of a primary key? I have a simple table with an
>> order_id and an enter_date as the PKs. But when I list the enter_date
>> in the Primary section, it comes back empty when I fetch records. All
>> other columns have data.
>>
>> If I put enter_date in Essential, it works. The problem is, if I have
>> several records with the same order_id, CDBI spits them all out with
>> the same date since they all look the same to CDBI.
>>
>> Thanks for any insight.
>>
>> Jim
>>
>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>> Jim Brandt
>> Administrative Computing Services
>> University at Buffalo
>>
>>
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> Jim Brandt
> Administrative Computing Services
> University at Buffalo
>
>
> =
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=20=

> "*"*"*
> Hinweis: Dieses E-mail kann vertrauliche und gesch=FCtzte =
Informationen =20
> enthalten.
> Sollten Sie nicht der beabsichtigte Empf=E4nger sein, verst=E4ndigen =
Sie =20
> bitte den Absender und l=F6schen Sie dieses E-mail dann sofort.
>
> Notice: This e-mail contains information that is confidential and may =20=

> be privileged.
> If you are not the intended recipient, please notify the sender and =20=

> then delete this e-mail immediately.
> =
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=20=

> "*"*"*
>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Jim Brandt
Administrative Computing Services
University at Buffalo