Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: date as a foreign key

Re: date as a foreign key

From: Philip Lijnzaad <>
Date: 26 Sep 2001 13:10:08 +0100
Message-ID: <>

> In article <8eIr7.64$>, David Cressey says...

>> Just a general comment on the use of timestamps. This comment may or may
>> not apply to the originator of this thread.

(the outcome was that an integer-type of time  should be fine, as long as you make sure you obtain its value once, and  use that ever after)

>> Real Numbers (floating point numbers) are a poor choice for indexes, and
>> therefore a poor choice for primary keys.
>> Although Oracle timestamps are not real numbers, they suffer from the same
>> problem somewhat. The problem is that we tend to treat "equality" of two
>> measurements as being a somewhat fuzzy concept. If the difference between
>> two measurements is less than some suitably small value, epsilon, we say
>> "close enough" and regard them as equal.

> Therefore, you insist upon a discrete domain for foreign key constraints only?

Sounds reasonable. Although I suspect that some applications (image analysis, time-dependent stuff, things involving physical measurements) migth have the need for continuous keys. I don't think you can call them foreign keys anymore, since by definition 'equals' a primary key, which by definition is unique, which can't be guaranteed if 'equals' is an interval operation.

I think constraint databases do these kinds of things. Anyone care to comment?

> Then, strings would probably be a poor choice as well, because the difference
> between the 2 could be "infinetely" small. Take "David Cressey" and "Dave
> Cressey", for example, isn't it pretty much much the same as 1.00000 and
> 1.00001? 

No, not at all. Strings are countable, reals are not. I.e., you set up a scheme to enumerate all the infinitely many strings; you cannot do that for reals, because you can always add an additional one between the n-th and (n+1)th. It clearly works for natural numbers and also for rationals. It would work for reals that are represented in fixed-length bit-patterns (actually do databases internally use IEEE 754 for that, or what?), but not in general.


The mail transport agent is not liable for any coffee stains in this message
Philip Lijnzaad, \ 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
Received on Wed Sep 26 2001 - 07:10:08 CDT

Original text of this message