Making data in a column unique (that's not a PK)

Question

Post reply

Making data in a column unique (that's not a PK)

RedBirdOBX

SSCommitted

Points: 1836
More actions
May 20, 2013 at 10:57 am

#272831

This may be an elementary question but if I don't ask, I won't learn.
What's the best way to ensure that data entered into a certian column is unique and not already in the table even though the column is NOT the PK. For example:
SalesmanID
SSN
FirstName
LastName
...etc...
SalesmanID is the PK but I do want SSN to be unique as well. Is there something in the table design I can do to ensure this or do I need to resort to a Trigger or Stored Proc?
Thanks in advance for offering help.

Viewing 14 posts - 1 through 13 (of 13 total)

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

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

There's a UNIQUE keyword in the DDL (table definition) that specifies a unique constraint.

http://www.w3schools.com/sql/sql_unique.asp

Terence Keys Ten Centuries Points: 1140 More actions · Answer 2

you can make it a uniqueidentifier field (GUID)

http://www.dailycoding.com/Posts/generate_new_guid_uniqueidentifier_in_sql_server.aspx

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

RedBirdOBX (5/20/2013)
What's the best way to ensure that data entered into a certian column is unique and not already in the table even though the column is NOT the PK.

Create an UNIQUE INDEX on such a column.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 4

Terence Keys (5/20/2013)
you can make it a uniqueidentifier field (GUID)
http://www.dailycoding.com/Posts/generate_new_guid_uniqueidentifier_in_sql_server.aspx

OK if it's just an identifier. I suspect SSN is Social Security Number though...

Terence Keys Ten Centuries Points: 1140 More actions · Answer 5

laurie-789651 (5/20/2013)
Terence Keys (5/20/2013)
you can make it a uniqueidentifier field (GUID)
http://www.dailycoding.com/Posts/generate_new_guid_uniqueidentifier_in_sql_server.aspx
OK if it's just an identifier. I suspect SSN is Social Security Number though...

ah.. Understood.. Thanks

RedBirdOBX SSCommitted Points: 1836 More actions · Answer 6

Would you be so kind as to provide an example?

SalesmanID (PK)

SalesmanSSN (ype, it's the social)

FirstName

LastName

...etc...

Sean Lange SSC Guru Points: 286589 More actions · Answer 7

RedBirdOBX (5/20/2013)
Would you be so kind as to provide an example?
SalesmanID (PK)
SalesmanSSN (ype, it's the social)
FirstName
LastName
...etc...

You can either add the unique keyword to the column definition or alter the table and add the constraint with a name.

However, do yourself a favor and don't store SSN in clear text. This type of data needs to be encrypted at rest.

Honestly since this is a Salesman table it probably should not be in that table at all. It should be in your employee table along with all of the other demographic information about that person.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 8

RedBirdOBX (5/20/2013)
This may be an elementary question but if I don't ask, I won't learn.
What's the best way to ensure that data entered into a certian column is unique and not already in the table even though the column is NOT the PK. For example:
SalesmanID
SSN
FirstName
LastName
...etc...
SalesmanID is the PK but I do want SSN to be unique as well. Is there something in the table design I can do to ensure this or do I need to resort to a Trigger or Stored Proc?
Thanks in advance for offering help.

Step 1 would be to stop storing SSN's as lear text! :Whistling:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100951 More actions · Answer 9

RedBirdOBX (5/20/2013)
Would you be so kind as to provide an example?
SalesmanID (PK)
SalesmanSSN (ype, it's the social)
FirstName
LastName
...etc...

ALTER TABLE dbo.tablename

ADD CONSTRAINT tablename__UQ_SalesmanSSN UNIQUE ( SalesmanSSN )

--of course you can name the constraint anything you want 🙂 if you don't like tablename__UQ_SalesmanSSN

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

RedBirdOBX SSCommitted Points: 1836 More actions · Answer 10

Thanks but don't have a choice in particular scenario....

Sean Lange SSC Guru Points: 286589 More actions · Answer 11

RedBirdOBX (5/20/2013)
Thanks but don't have a choice in particular scenario....

I would fight back hard on that one. It is irresponsible to store SSNs in clear text. Would you store credit cards in clear text? Why not? The same holds true for SSNs. I realize time constraints and all that other stuff are important but if you don't bring it up then you are as much to blame as the person who originally wrote it. In cases like this if you are not part of the solution you are part of the problem. At the very least make sure you protect yourself. Put it in writing to your boss that you think they need to be careful with this information. Include a copy to the CIO and HR. That way when the "you know what" hits the fan you can demonstrate that you wanted and tried to correct it but your boss dictated otherwise.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

RedBirdOBX SSCommitted Points: 1836 More actions · Answer 12

RedBirdOBX

SSCommitted

Points: 1836

May 21, 2013 at 8:10 am

#1617031

Good advice. Much appreciated. Will "CMA".

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 13

RedBirdOBX (5/20/2013)
Thanks but don't have a choice in particular scenario....

As Sean stated, you actually do and it's not just a matter of you doing a CYA. You can take it even further if no one you tell does anything about it. First, put yourself in the place of the people whose SSN's you're storing in clear text. Would you want it done to your personal data? The buck has to stop somewhere or it'll never stop. As a man much smarter than me once said, "All that is necessary for evil to succeed is for good men to do nothing."

Second, you CAN report the problem to authorities without your name ever being brought up thanks to things like the various "whistle blower" acts in various States and departments of the government (Social Security Administration, for example). If no one does anything about the SSN's being stored in clear text after you've told them about it, blow the whistle. You'd want your personal data to be protected. Why wouldn't you help others along the same lines? Don't say it's not your job because it's an unwritten law and a huge matter of ethics that DBAs are the last line of defense on such things. If you just do a CMA and move on, then you're really no better than the people who allow it. Don't let yourself fall into that category. Worse than that, if the company is found out, then even your CMA efforts may not be enough. You could still be made to suffer "implications" that could destroy your reputation and your career.

BTW... storing SSN's and other particular personal information in clear text is a violation of SEC, SOX, ISO, several Federal and Local Laws, not to mention common sense. BE the guardian of the data. Your career could depend on it.

Of course, giving your management the opportunity to fix the problem would be the very first thing to do. The CMA documentation will help you during that short period of time but only for that short time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)