May 20, 2013 at 10:57 am
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.
May 20, 2013 at 11:00 am
There's a UNIQUE keyword in the DDL (table definition) that specifies a unique constraint.
May 20, 2013 at 11:21 am
you can make it a uniqueidentifier field (GUID)
http://www.dailycoding.com/Posts/generate_new_guid_uniqueidentifier_in_sql_server.aspx
May 20, 2013 at 11:42 am
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.May 20, 2013 at 11:44 am
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...
May 20, 2013 at 11:47 am
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
May 20, 2013 at 12:12 pm
Would you be so kind as to provide an example?
SalesmanID (PK)
SalesmanSSN (ype, it's the social)
FirstName
LastName
...etc...
May 20, 2013 at 12:18 pm
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/
May 20, 2013 at 2:43 pm
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
Change is inevitable... Change for the better is not.
May 20, 2013 at 2:43 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 20, 2013 at 5:56 pm
Thanks but don't have a choice in particular scenario....
May 21, 2013 at 7:39 am
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/
May 21, 2013 at 8:10 am
Good advice. Much appreciated. Will "CMA".
May 25, 2013 at 2:31 pm
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
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply