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.
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.
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.