1

I am having some confusion on how to determine a primary key in regards to an ERD model.

Say for example, I created the following table to keep track of employees salary.

Sal_His(Emp#, Salary, Reason, Raise-Date)

How would I determine which key would become the primary key?

user104707
  • 23
  • 5
  • ask yourself: what makes a record unique? – Karoly Horvath Aug 29 '14 at 20:41
  • well an employee can have only one employee number, one salary. However, the employee can have several Raise-Dates due to increases in salary. So I would think that Raise-Date could be a potential primary key but am not positive. Is this even on the right track to what you were referring to? – user104707 Aug 29 '14 at 20:48
  • Raise-Date doesn't make a record unique - Several employes could have a raise in the same time. You have to combine the two fields. – Karoly Horvath Aug 29 '14 at 21:40

1 Answers1

0

A primary key can also be a combination of multilple fields.

In your case, Emp# and Raise-Date together might form the primary key.

EDIT At the logical level, those two fields form a compound primary key. That primary key indentifies uniquely each row of the table (unless an employee can have multiple raises per day) and is irreducible because none of those fields alone is sufficient to uniquely identify your records.

When you get to the physical level, you might want to introduce a surrogate primary Key (an ID) and create a unique index on the two columns (RaiseDate, Emp#). You can find more information about the benefits and drawbacks of this approach here.

Community
  • 1
  • 1
Ndech
  • 965
  • 10
  • 21
  • Can you please explain how you can combine two keys into one primary key. I was under the impression you could only have one primary key – user104707 Aug 29 '14 at 22:01
  • You can indeed have only one primary key but one primary key can be compound and reference multiple columns. I edited my answer with more details. – Ndech Aug 30 '14 at 07:31