SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Violation of PRIMARY KEY constraint
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Prosercunus
Starting Member

USA
22 Posts

Posted - 10/06/2012 :  17:10:01  Show Profile  Reply with Quote
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.

Edited by - Prosercunus on 10/06/2012 17:14:24

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/06/2012 :  18:48:45  Show Profile  Reply with Quote
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.

Edited by - sunitabeck on 10/06/2012 18:49:47
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/07/2012 :  12:59:17  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000