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

Date column as primary key

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

You can choose a date column for a primary key and by doing so the date + time (Hours , Minutes , seconds) will be entered as the primary key.

Example :

If table test as a primary key on column d, then the following insert are valid:

INSERT INTO test (d) VALUES (
	TO_DATE('19-Jul-2001 12:08:52','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO test (d) VALUES (
	TO_DATE('19-Jul-2001 12:08:55','DD-MON-YYYY HH24:MI:SS'));
INSERT INTO test  (d) VALUES (
	TO_DATE('19-Jul-2001 12:08:59','DD-MON-YYYY HH24:MI:SS'));

If in the same second 2 users are trying to enter SYSDATE to the primary key, we'll get a unique constraint error. The Primary key on a Date column is best used when you search in ranges, using BETWEEN, > ,< ,=>,<= operators. The big problem in using dates as primary keys is that it is extremely difficult to perform an exact search with the = operator: You must know the exact time up to seconds.

Read part two of this answer.


This was last published in July 2001

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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close