Violation of PRIMARY KEY constraint

Question

I am trying to record unique identifiers, so I cannot afford to have a duplicate record of my ID's

I am getting an error that looks like this when I try to update my SQL Server table called Clients.

Violation of PRIMARY KEY constraint 'PK_clients'. Cannot insert duplicate key in object 'db_owner.clients'.

The code for this looks like so:

public void Subscribe(string clientID, Uri uri)
{
    clientsDBDataContext clientDB = new clientsDBDataContext();
    var client = new ServiceFairy.clientURI();
    client.clientID = clientID;
    client.uri = uri.ToString();
    clientDB.clientURIs.InsertOnSubmit(client);
    clientDB.SubmitChanges();
}

Any Idea how I can go about fixing this, so I can update my rows, all I want to be able to do is when a row exists then only update the associated URI, and if it doesn't exist to submit a new clientID + URI,

Thanks

John

Joel C · Accepted Answer · 2011-07-23T15:45:14.450

What you want to do is first check for the existing record, and if it doesn't exist, then add a new one. Your code will always attempt to add a new record. I'm assuming you're using Linq2Sql (based on the InsertOnSubmit)?

public void Subscribe(string clientID, Uri uri)
{
    using(clientsDBDataContext clientDB = new clientsDBDataContext())
    {
        var existingClient = (from c in clientDB.clientURIs
                              where c.clientID == clientID
                              select c).SingleOrDefault();

        if(existingClient == null)
        {
            // This is a new record that needs to be added
            var client = new ServiceFairy.clientURI();
            client.clientID = clientID;
            client.uri = uri.ToString();
            clientDB.clientURIs.InsertOnSubmit(client);
        }
        else
        {
            // This is an existing record that needs to be updated
            existingClient.uri = uri.ToString();
        }
        clientDB.SubmitChanges();
    }
}

Thanks Joel thats a great example, makes figuring out how to integrate much easier, just having such an example makes my life much much easier haha — John Antony Daniel Nolan, Jul 22 '11 at 21:16
Contrary to your comment - Linq2Sql DOES support SingleOrDefault and that would be appropriate in this case to use it — Pleun, Jul 23 '11 at 09:53
My mistake, looks like I confused Linq2Sql with the earliest version of Entity Framework. I've updated my example. — Joel C, Jul 23 '11 at 15:44

cdhowie · Answer 2 · 2011-07-22T21:12:43.270

3

You need to obtain the existing object and update its uri property and then call clientDB.SubmitChanges(). The code you have right now quite explicitly asks it to insert a new record.

Presumably something like this would work:

using (clientsDBDataContext clientDB = new clientsDBDataContext()) {
    var client = clientDB.clientURIs.Where(c => c.clientID == clientID).Single();
    client.uri = uri.ToString();
    clientDB.SubmitChanges();
}

edited Jul 22 '11 at 21:12

answered Jul 22 '11 at 20:56

cdhowie

158,093
24
286
300

1

or if you have the object in the old state you can reattach it then modify it. Then submit the changes. – m4tt1mus Jul 22 '11 at 21:00

Violation of PRIMARY KEY constraint

2 Answers2

Linked