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: Aloha Kakuikanu <>
Date: Wed, 19 Sep 2001 17:46:45 GMT
Message-ID: <9S4q7.6021$>

In article <>, Philip Lijnzaad says...
>> I have a master
>> table Task (
>> start_date date unique key,
>> <parameters>
>> )
>it's also a bad idea to have dates as primary key (as happens here);
>is there really never ever going to be a chance that two tasks start on
>the same day?

Sorry, I missed important detail: DATE above is Oracle date, which is essentially a timestamp.

>Also when your company is bought and all systems have to be

No chance, it's very big;-) Also, because we speak about tasks rather than ordinary entities like customers, the chances that the other company has a "task" system is negligible as well.

>> and a detail
>> table TaskStatement (
>> start_date date foreign key references Run.start_date,
>> <etc>
>> )
>> I was told that it is bad idea to use date as a foreign key, but I didn't
>> hear a convincing argument.
>well, there's the above problem, and another one is that dates vary widely
>across the DBMSs, so porting is difficult.

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.

>> Use sequences instead? Well, much have been
>> said about surrogate keys already. We can start 2 tasks at the same time?
>> Not if lanching them manually;-)
>You have a very short working day :-)
>Hang on: did you say TIME ? or DATE? or TIMESTAMP or DATETIME ?

I'm happy they have a single type in Oracle, accomodating all those.

>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"?

>> 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. Received on Wed Sep 19 2001 - 12:46:45 CDT

Original text of this message