Re: date as a foreign key

Hello,

I cannot see the whole thread, so I'm probably missing things already said. Just my 0.2 cents: I know a couple of guys who set a time stamp as primary key. When starting testing their app, they got lots of integrity constraint messages from Oracle. Basically their test program was faster in inserts than the machine's clock. They were very proud of their "fix": let's put some sleep statements in the code.

"Philip Lijnzaad" <lijnzaad_at_ebi.ac.uk> wrote in message news:u7u1xyw1is.fsf_at_sol6.ebi.ac.uk...
>
> > Sorry, I missed important detail: DATE above is Oracle date, which is
> > essentially a timestamp.
>
> OK.
>
> > OK. However, this is essentially equivalent to saying that no
user-defined
> > domain type is desired in the RDBMS, as it can't be ported.
>
> (SQL standard defines DATE, DATETIME, TIME and TIMESTAMP, but in practice,
> they're not. That Oracle treats DATE and DATETIME as synonymous is wrong,
and
> annoying)
>
> >> Avoid those
> >> even more; I don't even think they are guaranteed to be equal to
anything,
> >> only '<' and '>' need to behave as expected.
>
> > Same as "real" numbers? Isn't there a way to construct 2 equal TIMESTAMP
> > values (NUMBERs, etc) for which equality guaranteed to return "TRUE"?
>
> I'm not sure; this should be documented by Oracle. The only thing you
have
> to be careful about is that you obtain the timestamp just once, and use
that
> in all your INSERT statements (this should be obvious, but I'm just saying
it
> because some people might be tempted to use SYSDATE in a few places,
getting
> different (nano?micro?milli?)seconds back)
>
> >>> besides, I put explicit constraint --
> >>> unique key -- to prevent this. Am I missing anything else?
> >>
> >> In all big systems I have seen, you sooner or later have to do
> >> interactive queries, and it's difficult to enter dates, rather than
> >> numbers or strings.
>
> > Well, it seems that the tradeoff is meaningless sequence number versus
> > somewhat less convenient date.
>
> that's true ... I think in your case I would go for a Unix timestamp
(number
> of seconds since 1970-01-01 00:00:00 ... 32 bit quantity, roles over in
2038,
> but by that time, systems are expected to all be 64 bits). This way it is
> both a timestamp, but has the advantage that it doesn't look like one.
>
> So, all in all, I now agree that in the circumstances you sketched, it
might
> be acceptable (or even useful) to use timestamp-like things instead of
> sequences. Comments anyone else ?
>
>
Philip
> --
> Real programs don't eat cache (Malay)
> --------------------------------------------------------------------------

---


> Philip Lijnzaad, lijnzaad_at_ebi.ac.uk \ European Bioinformatics Institute,rm


A2-08


> +44 (0)1223 49 4639                 / Wellcome Trust Genome Campus,


Hinxton


> +44 (0)1223 49 4468 (fax)           \ Cambridgeshire CB10 1SD,  GREAT


BRITAIN


>