How does one force a column (which is not the PK) to be unique?

Question

How does one force a column (which is not the PK) to be unique?

Ken at work

Hall of Fame

Points: 3426
More actions
April 12, 2012 at 9:27 am

#392009

I have a table with only two fields (columns).
strInitials - nvarchar(3)
Emp_No - int - Primary Key
However, I wish the strInitials to be unique, i.e. not repeated in the table.
Can I do this through a Constraint? What kinda of an expression might I add?
Thank you,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Ken at work (4/12/2012)
I have a table with only two fields (columns).
strInitials - nvarchar(3)
Emp_No - int - Primary Key
However, I wish the strInitials to be unique, i.e. not repeated in the table.
Can I do this through a Constraint? What kinda of an expression might I add?
Thank you,

You can create a unique index on the column. However, what would you do with two of my girls if they worked for you. Both of their initials are KAP.

Ken at work Hall of Fame Points: 3426 More actions · Answer 2

Hmm, that was so easy I'm embarrassed.

But then you had to go and bring reality into it.

How about KP1 and KP2? hehe

What would you suggest?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Maybe explain why you want to force something that is not naturally unique to be unique?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Close the two I mentioned when younger were actually K2 and K3. My oldest daughter was K1. The first names of all three of my daughters start with K.

Any way, I would not make strInitials unique. Doesn't really need to be since the emp_no is unique.

Ken at work Hall of Fame Points: 3426 More actions · Answer 5

Hi Gail, hope everything is well. How are things abroad?

Ah well, since you asked. I personally would not use this as an index for the very reason you stated. My preference is the Employee Number.

However I'm working withing the constrains of the department and this department has used initials to identify people on their projects and at this point their preference is not to change. Nevertheless I'm working on that and have already suggested they change identifiers.

If this had been a new system instead of a replacement product it wouldn't even have been an option.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

First thing would be to ask the department how they handle (or would handle) people with duplicate initials.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ken at work Hall of Fame Points: 3426 More actions · Answer 7

Yep, oddly enough that was the first thing I asked them when we got to this part.

And the answer is....

'Oh yeah, we had this problem before with a new hire. We had to change all the old ones to something else. We'll get back to you if we can switch to Emp No.'

🙂