Should I use time as part of a composite key or create a new primary key?

Question

I have a table in my database that consists of two foreign keys, which together are still not unique, but adding a time/date to them would make them unique. Is it a good idea to use those three columns together as the primary key or should I just create a new column and use a separate primary key?

I think it's a better idea to use a surrogate key generated by the database as the primary key in this case. — Leo, Nov 10 '14 at 18:33
How would a time/date make it unique? Like Leo said, add a generated key. — ForguesR, Nov 10 '14 at 18:36
That's what I was leaning towards but I'm new to database design so I just wanted to make sure. — user4181107, Nov 10 '14 at 18:36
@ForguesR yeah, it seems a little more obvious now, I'm going to add a separate column for a primary key. — user4181107, Nov 10 '14 at 18:41
If you don't have columns that can make up a primary key in the first place, what is a surrogate id a surrogate *for*? — Mike Sherrill 'Cat Recall', Nov 10 '14 at 19:00

score 1 · Accepted Answer · answered Nov 10 '14 at 18:40

Adding a timestamp to artificially create a composite primary key from other 2 columns is not a good idea IMO because you can have HW fast enough to generate two identical composite keys in your database.

Instead, you may check your database for a surrogate key generator (usually some sort of sequence) in this case.

score 0 · Answer 2 · edited May 23 '17 at 12:31

0

NO: adding a `time based value to build a primary key can not valid as you can understand from here.

It is better to create a primary key of its own to your table.

edited May 23 '17 at 12:31

Community

1
1

answered Nov 10 '14 at 18:45

Should I use time as part of a composite key or create a new primary key?

2 Answers2