What to use as a Primary Key of SQL Database of dates

I am making a SQLite database with Java interface where the user can input information for different dates. Basically its like a diary. But I am having trouble constructing a table for the database. I have fields called year (eg 2011), month (from 1 to 12), dayofmonth (from 1 to 31) and other fields relating to user input. But none of these are any good for a primary field. I thought of concatenating yearmonthdayofmonth so January 11 2011 is 20111101 (making sure values less than 10 have a zero in front, so it isnt the same as November 1st 2011, for example). But this seems clumsy, even if it 开发者_JS百科returns unique numbers. Is there a beter way you can recommend?

I don't think the date is actually a good primary key for your table, what happens when your user wants to do more than one thing on one day? What happens when he decides to have Pizza on Saturday rather than Friday?

Use an autoincrement primary key, store the eventDate as a single integer using the Datetime() function and define a secondary index for that column.

Consider using a simple auto-incrementing integer as the primary key. Then you can enforce uniqueness with a unique index, like;

create unique index UX_YourTable_Date on YourTable(yearCol, monthCol, dayCol)

This keeps the primary key small, avoids a computed or composite key, but still ensures correctness.

Well one way would be to start counting the number of days elapsed since a fixed date. i.e pick 1st January 2011 as Day 0, then 2nd Jan 2011 is Day 1, and so on...

This should work if you have some idea of the minimum date that the user will use, which you can set as day 0.

Don't use date as a primary key in your table. It's a bad idea and you will end up in primary key violation error if user enters more than one entry per day. Have a separate auto increment or some other unique value column. Use the date column to select records for a particular date.