Any issues with primary key as date/time column

  • Thread starter Thread starter mscertified
  • Start date Start date
Philosophically, provided there's no way you could have two (or more)
records with identical values in that field, I don't see why not.
Practically, or from a database standpoint, I don't know. Have you tried
searching on-line?

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
How many users entering records at the same time?
Will you be importing data into that table?
Could you ever be inserting records after midnight?

If it's only one or a few users there is little chance of inserting that two
or more new records at the same second.

If you import data into the table, you'll have to make sure that the data
has a date/time field without duplicate records.

During the Daylight Savings Time change in the Fall, you could possibly have
the same date/time stamp for different records as the time falls back an hour.

Other than that, it shouldn't be a problem. You could set the default value
for any new records to Now().

Personally I perfer autonumbers for PKs as they should be unique compared to
any other record in the table and don't have a 'meaning'. Date/Time just
might and someone may say that the record should have been entered yesterday
so change it. Not good.

On the other hand if the database is used by many people, it could be a
problem. Consider if EBay or Amazon would make the date/time a primary key
for one of their bigger tables. They couldn't add more than 60 sales a minute
for example.
 
This is normally not a good idea.

Discussion follows, but the main thing, speaking as the voice of
experience, is that it is possible that later you will want to use an
append query with multiple records as a data source, and all the
Date/Time values will be identical, forcing you to jump though
hoops to enter the records.

Anyway, more theoretical objections:

In a JET/MDB database, date/time is stored as a real (floating point)
number.

Floating point numbers are not a good choice of primary key because
they are not exact. You may have two floating point numbers that are
the same, but different. Or you may have two floating point numbers
that are different, but the same.

If you are only using the DATE part of the date/time, this is less of an
issue. Floating point numbers still make inefficient keys: there is a lot
of overhead in handling floating point numbers, but at least they won't
accidentally clash or get lost.

I have to say that in real life, with a JET/MDB database, using real
dates and times, you probably won't see this as a problem, with the
current version of JET. Still, that doesn't make it a good idea.

Also, as a secondary consideration, Date/Time and floating point numbers
are a really bad choice for use with SQL Server. SQL Server Date/Times
are stored differently, so none of your Date/Times would match exactly,
and floating point numbers don't always translate exactly either, so you
couldn't fake it that way either.

Again, you might not see this problem until you add the automated interface
or web interface, and it tries to add multiple records at the same time, but
for Access/SQL Server it is just a really bad idea anyway, and you want to
avoid it.

(david)


mscertified said:
Are there any issues in having a date/time field as my primary key?
Click to expand...
thanks.
 
David Epsom Dot Com Dot au said:
In a JET/MDB database, date/time is stored as a real (floating point)
number.
Click to expand...

Agreed with the snipped explanation. You did a better job of
describing those issues than I would've.

Also what if there are child tables with this table? <shudder>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Back
Top