c# - 违反 PRIMARY KEY 约束

Question

我正在尝试记录唯一标识符，所以我无法承受重复记录我的 ID

当我尝试更新名为 Clients 的 SQL Server 表时，我收到类似这样的错误。

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

代码如下:

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();
}            

知道如何解决这个问题，这样我就可以更新我的行，我想做的就是当行存在时只更新关联的 URI，如果它不存在则提交一个新的 clientID + URI，

谢谢

约翰

Answer 1

您要做的是首先检查现有记录，如果不存在，然后添加一条新记录。您的代码将始终尝试添加新记录。我假设您正在使用 Linq2Sql(基于 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();
    }
}