Problem solve Get help with specific problems with your technologies, process and projects.

Date column as primary key, part 2

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.

See another response to this question in part one.

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.