Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Prosercunus
Starting Member
22 Posts |
 Posted - 2012-10-06 : 17:10:01
|
| So I am suppose to make one of the table columns as my primary key. but this particular column in the table has inputs with repeating numbers.This of course gives me the error Violation of PRIMARY KEY constraint.Which I understand why, but the requirements want me to make this a key? And no table has the same column so it is not a foreign key?How do I work around this or see this in another light? I can't change the duplicate entry(s) for that column.I guess what I am asking is how do I make those duplicate entries be an exception while retaining the primary key status of that column in the table. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-06 : 18:48:45
|
| Not having duplicate values (along with the column being non-nullable - see here :http://msdn.microsoft.com/en-us/library/ms181043(v=sql.105).aspx ) is a requirement for a primary key column.As I see it, your choices are:a) Create a surrogate column (such as a column with identity property) to be the primary key.b) Examine if there are other columns in the table that when taken together with your column of interest would satisfy the conditions specified in the link that I posted earlier. If there are, make a composite primary key out of those columns.c) Create another surrogate column that when taken together with your existing column will have no duplicates.More often than not, when you find yourself in a situation where there are no candidate keys, it is an indication that there may be room for design improvements. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-07 : 12:59:17
|
quote:
Originally posted by Prosercunus
So I am suppose to make one of the table columns as my primary key. but this particular column in the table has inputs with repeating numbers.This of course gives me the error Violation of PRIMARY KEY constraint.Which I understand why, but the requirements want me to make this a key? And no table has the same column so it is not a foreign key?How do I work around this or see this in another light? I can't change the duplicate entry(s) for that column.I guess what I am asking is how do I make those duplicate entries be an exception while retaining the primary key status of that column in the table.
I would say then your requirements are not in line with how the current system is as if it wanted it to be primary key there shouldnt be duplicates in it in the first place.Now only solution is to either look for surrogate key as Sunita suggested or make primary key composite by adding other columns also which will be make combination unique------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Violation of PRIMARY KEY constraint