CODEWITHSUNDEEP
sql-serverdatabase-design
Zvi Twersky
Zvi Twersky

Reputation: 439

Unique constraint to combination of two columns unclustered index

I'm not asking HOW to do this, but if it's what I SHOULD be doing.

Two employees can be working on the same job. So of course, both FKs, EmployeeID and JobID, can have a MANY relationship in a "Employee_Jobs" table.

Let's take Employee A, Employee B, Job A and Job B. All of the following would be acceptable:

A A
A B
B A
B B

What would NOT be acceptable is a duplicate of a combination of these two PKs... since we cannot have for example, [Employee A working on Job A] twice.

So would it be correct to say that the only way to manage this is to make the combination of the two PKs, EmployeeID and JobID, a Unique, non-clustered index?

I tried to think of how to instead, break this up to more tables but I keep getting back to this same problem.

Upvotes: 0

Views: 191

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146541

Yes, not only is it appropriate, but in fact, the combination of these two attributes should be the PRIMARY KEY.

and in any other table where the entity represented by rows in the table has a logical attribute (consisting of the two columns employeeId and JobId), which represents the work done by an employee on a job, (or the contribution of the employee to a job, or the association of an employee to a job in any way), a FK in that table should be a composite Foreign Key consisting of these same two columns.

If you are using a surrogate key on this table to simplify joins and definition of Foreign Keys in other tables, then by all means continue to do so, but keep the two-column natural key in this table, as either a unique index or a Alternate Key. (a Key is a Key - anything that is declared or defined to be unique) so as to ensure data integrity in this table. In fact, to make it clear to users of the schema, when this situation comes up, I generally make the composite Natural Key the PRIMARY KEY, and add/define the surrogate (which is used in Joins and Other table FKs), as an alternate key or unique index. This is pretty much only a semantic distinction, only as they create almost identical functionality. But because data integrity is more important to me than join syntax and Foreign Key structure, To me, the Natural Key is the PRIMARY key,

Upvotes: 1

Rahul
Rahul

Reputation: 77896

Yes, In that case you should consider making both those fields as primary key; in specific a composite primary key or compound primary key like below which will make sure uniqueness of combination of both the fields.

primary key (EmployeeID , JobID)

Though as you said a Unique, non-clustered index but marking both the field as primary key will create a UNIQUE Clustered Index on them actually.

Upvotes: 1

Related Questions