Hi Tom:

Is there any disadvantages using DATE datatype as part of Primary Key. I got this information from one of my colleagues, taken from the net, source of which I don't know:

<quote>
Are there any problems if I select a date column as a primary key for an Oracle 8i database?

There are many problems when having a date field as a primary key:
a) Exact fetch is practically impossible to do.
b) Referential integrity is very, very hard to accomplish.
c) In most cases you will have to do a convert function on the column and that will eliminate the use of an index.

What I suggest is to:

First, if you can, do not choose a date; choose number(8) or number(12) and when you update the field,
use to_number(sysdate,'yyyymmdd')

Second, to enforce a foreign key, when you update the father in a transaction, save the date/time entered in a temporary variable, and when updating the child, use the same variable.

If you must carry with you the info about date and time, use another column number(4) with the hh and mm value.

Hope that's a help.
</quote>

How far is this reasoning valid? Or is this one of the Great Myths of Database Design? If indeed its so, any suggestions as to how we can best design the database?

Thanks for your time.

Take care,
Kishan.

Thank you very much. I reckoned that if a date column is not supposed to be used as PK, then Oracle would certainly not allow it. But a word from an authority, it surely helps.. :-) Thanks..

Hi tom,

why would you use between operator over
date_field >= to_date( '01-jan-2000' )
and date_field <= to_date( '01-jan-2000' ) + 1-1/24/60/60


Thanks,

Hi tom,

thanks, for answring the question.
will the performance be same using between and
two disjoint stmts ?
also that is primary key attributes
and about 1 mil records ( will it use index ?)


Thanks,

Hi Tom,

I think the point being made by Kishan's comment above is that if you needed to use just the date (and NOT the time component of the date) as part of a primary key, then you're hosed since every insert with a 1-second interval will make it into the table. To demonstrate what I mean:

create table t2 (x varchar2 (1), y int, z date, constraint
pk_t2 primary key (y, z) )
/

insert into t2 values ('a', 111, sysdate )
/
insert into t2 values ('a', 111, sysdate )
/
insert into t2 values ('a', 111, sysdate )
/
insert into t2 values ('a', 111, sysdate )
/

All of the above inserts, assuming a one-second gap, will make it into the table, whereas the requirement might have been to fail all but the first insert in my example above. I've run into this situation a few times, and some database designers and modelers have suggested excluding dates from the primary key for such a requirement. And then the only avenue is to use a formatted string that includes just the date component, such as "to_char (sysdate, 'MMDDYYYY')", as part of the primary key, instead of the date. I hope I've been able to explain myself... I think this is what was being attempted to be explained in the comments above. Any thoughts? Thanks.

Firstly, thank you for your knowledge and your perspective on our issues. (patience as well...)

I am presently in debate with a colleague and want your opinion. Presently, we have a table:

LOCATION_ID VARCHAR2(10)
LOCATION_NAME VARCHAR2(55)
FISCAL_YEAR_ID VARCHAR2(4)
REPORT_ID VARCHAR2(5)

The customer wants to alter the FISCAL_YEAR_ID,(example '2004'), to MANIFEST_CREATE_DATE DATE. My colleague is supporting this change, but I disagree. Before, the report identifiers were character representations of a sequence, that would reset at the end of the fiscal year. The customer has not expressed an interest in deviating from this method of fiscal identifier generation. With altering the identifier to a date, you now have three logical breaks instead of one. (ie. DAY, MONTH, and YEAR) The date field is a value that has three parts (six if you use time information). This allows the customer to accidentally create duplicate report identifiers in the same fiscal year because it was entered on the following day. There is no way to guarantee the consistency of and quality of your data in the table any longer. Now, the report identifier can accidentally be re-generated daily and not violate the PK constraint. The argument of the customer was that it would give them more flexibility with their data down the line. Why can't this be accomplished with a creation date field below the line with an index built upon it? So, with this being said, isn't a date (DD-MON-YYYY) actually 3 values that are analyzed separately and then placed as one value in the field? Shouldn't it be said that a date datatype cannot be used as a single actual value in a table, but is actually a concatenation of different measurements that is only displayed as one value? Also, please explain the difference between having a four digit character representation of a fiscal year and having a date in this primary key. (Thank you in advance)

I apologize Tom, I only listed the Primary Key attributes for my example to avoid confusion... The four attributes above make up the primary key for the table. Location Identifier is an abstract identifier given to a specific geophysical place, the Location Name is necessary because a specific geophysical location may be identified by different names (i.e. saint louis regional airport has a military and a civilian portion that are both uniquely identified by the FAA, even though it is the same geophysical location). Fiscal Year Identifier is self explanatory as a four digit representation of a fiscal year (i.e. '2004'). Report Identifier is a sequence that is generated for each new report generated in a fiscal period, and is set to zero each year on October 1st.
The seven byte makeup of the data contained in a date column is understood. As I understand, it is binary string whose value identifies a specific day, month, and year. What I am trying to resolve is this: 1) Oracle sees this binary value and interprets its uniqueness of these three measures: year, month, and day. If these three interpreted values are conceptually analyzed differently to give uniqueness to each record that contains the seven byte, binary string, date datatype, then isn't it conceptually three different values? Although is is stored as one string, doesn't Oracle interpret particular parts of this string to identify each of the three measures, and then analyzes and builds the index accordingly? 2) It would allow for a particular Location/Location Name to generate a Report Identifier that is the same value (i.e. '00001') as one generated the day before. The smallest aggregate of the date field is day, and the business measure is fiscal which is defined as annual. In this case, isn't the date field too aggregate for this Primary Key to contain? If you already have uniqueness in your primary key, couldn't a date field give too much aggregation? I perfectly understand implementing a date below the line from a pro-active standpoint because you project to reach that level of aggregation eventually, but if there is no present requirement why do it? I'm worried about possible compromising of uniqueness by giving a level of aggregation that is not presently a business requirement.

Thank you very much Tom. I understand you are an extremely busy man and appreciate you taking the time to answer my questions.

In terms of bytes taken by a date date type, In all the documentation it states taht it takes 7 bytes. But, a light confusion when I compare the size of date between vsize and dump values, one shows 7 and the other 8. Am I naively comparing apples and toaster ovens?.

1 select sysdate as the_date,
2 vsize(sysdate) as vsize_storage,
3* dump(sysdate) as dump_value from dual
saqib@ORSBDS1R> /

THE_DATE VSIZE_STORAGE DUMP_VALUE
-------------------- ------------- ----------------------------------------
01-OCT-08 7 Typ=13 Len=8: 7,216,10,1,11,10,59,0

vsize_storage=7
dump_value (Len) = 8

ops$tkyte%ORA11GR1> create table t ( x date );

Table created.

ops$tkyte%ORA11GR1> insert into t values ( sysdate );

1 row created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select dump(sysdate), dump(x) from t;

DUMP(SYSDATE)
-------------------------------------------------------------------------------
DUMP(X)
-------------------------------------------------------------------------------
Typ=13 Len=8: 216,7,10,1,12,44,42,0
Typ=12 Len=7: 120,108,10,1,13,45,43

Got my answer here. Should've searched the site more. :) http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8769090385739

Thanks

Is there any issue having a string with large number of characters as primary key in the table. The referred table has a primary key is a combination of a date column (yyyymmddhh24miss) and other 2 columns (6 char long and 8 char long) which are already stored in the table.

Hi Tom,

Can you give a us a practical example to prove why it's bad?

E.g.
bad index selectivity,
more query logical reads,
more CPU used,
more latches,
hard to reference in child tables,
hard to maintain,
...

Question Authority. ^_^

Thanks,
Charlie

Note: it is an Data Warehouse application table.

Such as a sales fact table:

Primary Key: (order_date_year_month, location_id, product_id)

Thanks for the clarification.

I thought about the point of natural key on OLTP table, forget to jot it down.

Hello sir,

Could you please explain what is the risk of using date as primary key?


Thanks