Simon.S
Reputation: 41
Violation of primary key constraint error when UPDATE
I have the following code:
using (var connection = new SqlConnection(connetionString))
{
try
{
connection.Open();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
Console.WriteLine("DatabasConnection Done");
DateTime datum = DateTime.Now;
string LastChangedBy = "System";
foreach (Person p in myPersons)
{
SqlCommand command1 = new SqlCommand();
try
{
command1 = Avreg(p.UnregistrationReason, p.GivenNameNumber, p.ProtectedIdentity, p.CitizenshipDate, connection);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
Console.WriteLine("LALALALA Done");
command1.Parameters.AddWithValue("@PersonalIdentityNumber", string.Format("{0}{1}", p.PersonalIdentityNumber, p.SpecialIdentityNumber));
command1.Parameters.AddWithValue("@FirstName", p.FirstName ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@LastName", p.LastName ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@NationalRegistrationCountyCode", p.NationalRegistrationCountyCode ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@NationalRegistrationMunicipalityCode", p.NationalRegistrationMunicipalityCode ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@NationalRegistrationDistributionAddress1", p.NationalRegistrationDistributionAddress1 ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@NationalRegistrationDistributionAddress2", p.NationalRegistrationDistributionAddress2 ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@NationalRegistrationPostCode", p.NationalRegistrationPostCode ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@NationalRegistrationCity", p.NationalRegistrationCity ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@BirthCountyCode", p.BirthCountyCode ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@UnregistrationDate", p.UnregistrationDate ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@BirthParish", p.BirthParish ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@CitizenshipCode", p.CitizenshipCode ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@CitizenshipDate", p.CitizenshipDate ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@NationalRegistrationDate", p.NationalRegistrationDate ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@ForeignDistrubtionAddress1", p.ForeignDistrubtionAddress1 ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@ForeignDistrubtionAddress2", p.ForeignDistrubtionAddress2 ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@ForeignDistrubtionAddress3", p.ForeignDistrubtionAddress3 ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@ForeignBirthCity", p.ForeignBirthCity ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@LastChangedBy", LastChangedBy);
//command1.Parameters.AddWithValue("@LastChangedDate", datum);
command1.ExecuteNonQuery();
Console.WriteLine(string.Format("{0}{1}", p.PersonalIdentityNumber, p.SpecialIdentityNumber));
}
}
Console.WriteLine("Done");
Console.WriteLine("Alla fält uppdaterade");
Console.ReadKey();
}// Put a break-point here, then mouse-over PersonalIdentityNumber... deserializedList contains everything if you need it
catch (Exception e)
{
Console.Write(" ---- FEL VID INLÄSNINGEN ------ " + e.Message);
Console.ReadKey();
}
}
public static SqlCommand Avreg(string s, string t, string p, string c, SqlConnection connection)
{
try
{
var query = "UPDATE Seamen SET FirstName = @FirstName";//, "+
//"LastName = @LastName, "+
//"NationalRegistrationCountyCode = @NationalRegistrationCountyCode, "+
//"NationalRegistrationMunicipalityCode = @NationalRegistrationMunicipalityCode, "+
//"NationalRegistrationDistributionAddress1 = @NationalRegistrationDistributionAddress1, "+
//"NationalRegistrationDistributionAddress2 = @NationalRegistrationDistributionAddress2, "+
//"UnregistrationDate = @UnregistrationDate, "+
//"NationalRegistrationPostCode = @NationalRegistrationPostCode, "+
//"NationalRegistrationCity = @NationalRegistrationCity, "+
//"BirthCountyCode = @BirthCountyCode, "+
//"BirthParish = @BirthParish, "+
//"CitizenshipCode = @CitizenshipCode, "+
//"CitizenshipDate = @CitizenshipDate, " +
//"NationalRegistrationDate = @NationalRegistrationDate, "+
//"ForeignDistrubtionAddress1 = @ForeignDistrubtionAddress1, "+
//"ForeignDistrubtionAddress2 = @ForeignDistrubtionAddress2, "+
//"ForeignDistrubtionAddress3 = @ForeignDistrubtionAddress3, "+
//"ForeignBirthCity = @ForeignBirthCity, "+
//"LastChangedBy = @LastChangedBy, "+
//"LastChangedDate = @LastChangedDate";
SqlCommand command1;
if (c == "0")
{
query += ", CitizenshipDate = null";
command1 = new SqlCommand(query, connection);
}
else
{
query += ", CitizenshipDate = @CitizenshipDate";
command1 = new SqlCommand(query, connection);
command1.Parameters.AddWithValue("@CitizenshipDate", c ?? DBNull.Value.ToString());
}
if (p == "J")
{
query = "UPDATE Seamen SET FirstName ='Skyddad personuppgift'";//, " +
//"LastName = 'Se hjälptext', " +
//"ProtectedIdentity = '1', " +
//"NationalRegistrationCountyCode = NULL, " +
//"NationalRegistrationMunicipalityCode = NULL, " +
//"NationalRegistrationCoAddress = NULL, " +
////"NationalRegistrationDistributionAddress1 = NULL, " +
////"NationalRegistrationDistributionAddress2 = NULL, " +
//"UnregistrationDate = NULL, " +
//"NationalRegistrationPostCode = NULL, " +
//"NationalRegistrationCity = NULL, " +
//"BirthCountyCode = NULL, " +
//"BirthParish = NULL, " +
//"CitizenshipCode = NULL, " +
// //"CitizenshipDate = @CitizenshipDate, " +
//"NationalRegistrationDate = NULL, " +
//"ForeignDistrubtionAddress1 = NULL, " +
//"ForeignDistrubtionAddress2 = NULL, " +
//"ForeignDistrubtionAddress3 = NULL, " +
//"UnregistrationReason = NULL, " +
//"ForeignBirthCity = NULL, " +
//"LastChangedBy = @LastChangedBy, " +
//"GivenNameNumber = NULL, " +
//"LastChangedDate = @LastChangedDate WHERE PersonalIdentityNumber = @PersonalIdentityNumber";
command1 = new SqlCommand(query, connection);
command1.Parameters.Clear();
return command1;
}
if ((!string.IsNullOrEmpty(s)) && !string.IsNullOrEmpty(t))
{
query += ", UnregistrationReason = @UnregistrationReason";
query += ", GivenNameNumber = @GivenNameNumber ";
command1 = new SqlCommand(query, connection);
command1.Parameters.AddWithValue("@UnregistrationReason", s ?? DBNull.Value.ToString());
command1.Parameters.AddWithValue("@GivenNameNumber", t ?? DBNull.Value.ToString());
}
else if (!string.IsNullOrEmpty(s) && string.IsNullOrEmpty(t))
{
query += ", UnregistrationReason = @UnregistrationReason, GivenNameNumber = @GivenNameNumber WHERE PersonalIdentityNumber = @PersonalIdentityNumber";
command1 = new SqlCommand(query, connection);
t = "00";
command1.Parameters.AddWithValue("@UnregistrationReason", s ?? DBNull.Value.ToString());
command1.Parameters.AddWithValue("@GivenNameNumber", t ?? DBNull.Value.ToString());
}
else if (string.IsNullOrEmpty(s) && !string.IsNullOrEmpty(t))
{
query += ", GivenNameNumber = @GivenNameNumber WHERE PersonalIdentityNumber = @PersonalIdentityNumber";
command1 = new SqlCommand(query, connection);
command1.Parameters.AddWithValue("@GivenNameNumber", t ?? DBNull.Value.ToString());
}
else
{
query += ", GivenNameNumber = @GivenNameNumber WHERE PersonalIdentityNumber = @PersonalIdentityNumber";
t = "00";
command1 = new SqlCommand(query, connection);
command1.Parameters.AddWithValue("@GivenNameNumber", t ?? DBNull.Value.ToString());
return command1;
}
return command1;
}
catch(Exception e)
{
throw;
}
}
//}
The error occurs here:
command1.Parameters.AddWithValue("@PersonalIdentityNumber", string.Format("{0}{1}", p.PersonalIdentityNumber, p.SpecialIdentityNumber));
command1.Parameters.AddWithValue("@FirstName", p.FirstName ?? DBNull.Value.ToString());
//command1.Parameters.AddWithValue("@LastName", p.LastName ?? DBNull.Value.ToString());
command1.ExecuteNonQuery();
Console.WriteLine(string.Format("{0}{1}", p.PersonalIdentityNumber, p.SpecialIdentityNumber));
I get error:
Violation of Primary key constraint 'PK_logSeamen'. Cannot insert duplicate key in object 'dbo.logSeamen'. The statement has been terminated.
Do I need need to clear the SqlCommand?
Apperently I had this trigger
USE [Bums]
GO
/****** Object: Trigger [dbo].[trSeamen_LogI] Script Date: 05/16/2016 11:43:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trSeamen_LogI] ON [dbo].[Seamen] AFTER INSERT AS
INSERT INTO [logSeamen] ([PersonalIdentityNumber], [ProtectedIdentity], [ReferedCivicRegistrationNumber], [UnregistrationReason], [UnregistrationDate], [MessageComputerComputer], [GivenNameNumber], [FirstName], [MiddleName], [LastName], [NotifyName], [NationalRegistrationDate], [NationalRegistrationCountyCode], [NationalRegistrationMunicipalityCode], [NationalRegistrationCoAddress], [NationalRegistrationDistributionAddress1], [NationalRegistrationDistributionAddress2], [NationalRegistrationPostCode], [NationalRegistrationCity], [NationalRegistrationNotifyDistributionAddress], [NationalRegistrationNotifyPostCode], [NationalRegistrationNotifyCity], [ForeignDistrubtionAddress1], [ForeignDistrubtionAddress2], [ForeignDistrubtionAddress3], [ForeignDistrubtionCountry], [ForeignDate], [BirthCountyCode], [BirthParish], [ForeignBirthCity], [CitizenshipCode], [CitizenshipDate], [Email], [Telephone], [Mobiletelephone], [Gender], [NotNewsPaper], [Note], [StatusCode], [NationalRegistrationCode], [RegistrationDate], [LastUpdatedFromNavet], [TemporaryDistrubtionAddress1], [TemporaryDistrubtionAddress2], [TemporaryDistrubtionAddress3], [TemporaryDistrubtionCountry], [Password], [VisibilityLevel],[SeamanIdentity], [LastChangedBy], [LastChangedDate], LogAction)
SELECT [PersonalIdentityNumber], [ProtectedIdentity], [ReferedCivicRegistrationNumber], [UnregistrationReason], [UnregistrationDate], [MessageComputerComputer], [GivenNameNumber], [FirstName], [MiddleName], [LastName], [NotifyName], [NationalRegistrationDate], [NationalRegistrationCountyCode], [NationalRegistrationMunicipalityCode], [NationalRegistrationCoAddress], [NationalRegistrationDistributionAddress1], [NationalRegistrationDistributionAddress2], [NationalRegistrationPostCode], [NationalRegistrationCity], [NationalRegistrationNotifyDistributionAddress], [NationalRegistrationNotifyPostCode], [NationalRegistrationNotifyCity], [ForeignDistrubtionAddress1], [ForeignDistrubtionAddress2], [ForeignDistrubtionAddress3], [ForeignDistrubtionCountry], [ForeignDate], [BirthCountyCode], [BirthParish], [ForeignBirthCity], [CitizenshipCode], [CitizenshipDate], [Email], [Telephone], [Mobiletelephone], [Gender], [NotNewsPaper], [Note], [StatusCode], [NationalRegistrationCode], [RegistrationDate], [LastUpdatedFromNavet], [TemporaryDistrubtionAddress1], [TemporaryDistrubtionAddress2], [TemporaryDistrubtionAddress3], [TemporaryDistrubtionCountry], [Password], [VisibilityLevel], [SeamanIdentity], [LastChangedBy], [LastChangedDate], 'INSERT' FROM INSERTED
How can I make sure it doesn't violate the primary key constraint?
UPDATE 2
USE [Bums]
GO
/****** Object: Trigger [dbo].[trSeamen_LogU] Script Date: 05/16/2016 12:53:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trSeamen_LogU] ON [dbo].[Seamen] AFTER UPDATE AS
INSERT INTO [logSeamen] ([PersonalIdentityNumber], [ProtectedIdentity], [ReferedCivicRegistrationNumber], [UnregistrationReason], [UnregistrationDate], [MessageComputerComputer], [GivenNameNumber], [FirstName], [MiddleName], [LastName], [NotifyName], [NationalRegistrationDate], [NationalRegistrationCountyCode], [NationalRegistrationMunicipalityCode], [NationalRegistrationCoAddress], [NationalRegistrationDistributionAddress1], [NationalRegistrationDistributionAddress2], [NationalRegistrationPostCode], [NationalRegistrationCity], [NationalRegistrationNotifyDistributionAddress], [NationalRegistrationNotifyPostCode], [NationalRegistrationNotifyCity], [ForeignDistrubtionAddress1], [ForeignDistrubtionAddress2], [ForeignDistrubtionAddress3], [ForeignDistrubtionCountry], [ForeignDate], [BirthCountyCode], [BirthParish], [ForeignBirthCity], [CitizenshipCode], [CitizenshipDate], [Email], [Telephone], [Mobiletelephone], [Gender], [NotNewsPaper], [Note], [StatusCode], [NationalRegistrationCode], [RegistrationDate], [LastUpdatedFromNavet], [TemporaryDistrubtionAddress1], [TemporaryDistrubtionAddress2], [TemporaryDistrubtionAddress3], [TemporaryDistrubtionCountry], [Password], [VisibilityLevel], [SeamanIdentity],[LastChangedBy], [LastChangedDate], LogAction)
SELECT [PersonalIdentityNumber], [ProtectedIdentity], [ReferedCivicRegistrationNumber], [UnregistrationReason], [UnregistrationDate], [MessageComputerComputer], [GivenNameNumber], [FirstName], [MiddleName], [LastName], [NotifyName], [NationalRegistrationDate], [NationalRegistrationCountyCode], [NationalRegistrationMunicipalityCode], [NationalRegistrationCoAddress], [NationalRegistrationDistributionAddress1], [NationalRegistrationDistributionAddress2], [NationalRegistrationPostCode], [NationalRegistrationCity], [NationalRegistrationNotifyDistributionAddress], [NationalRegistrationNotifyPostCode], [NationalRegistrationNotifyCity], [ForeignDistrubtionAddress1], [ForeignDistrubtionAddress2], [ForeignDistrubtionAddress3], [ForeignDistrubtionCountry], [ForeignDate], [BirthCountyCode], [BirthParish], [ForeignBirthCity], [CitizenshipCode], [CitizenshipDate], [Email], [Telephone], [Mobiletelephone], [Gender], [NotNewsPaper], [Note], [StatusCode], [NationalRegistrationCode], [RegistrationDate], [LastUpdatedFromNavet], [TemporaryDistrubtionAddress1], [TemporaryDistrubtionAddress2], [TemporaryDistrubtionAddress3], [TemporaryDistrubtionCountry], [Password], [VisibilityLevel], [SeamanIdentity],[LastChangedBy], [LastChangedDate], 'UPDATE' FROM INSERTED
Upvotes: 0
Views: 1083
Answers (3)
Pradeep Kumar
Reputation: 6969
Updates can happen many times on a table. Your logSeamen table is meant to track changes, and it is always possible that the row being updated was already updated sometime in the past. You can not mark PersonalIdentityNumber (or any column from Seamen table) as Primary Key in logSeamen table.
To fix the problem,
Remove primary key constraint from PersonalIdentityNumber column in the logSeamen table, so that you can insert same PersonalIdentityNumber multiple times.
Optionally, add a new column (say logSeamenId) as primary key with INDENTITY(1,1), which would auto-generate a number for that row.
Upvotes: 0
Cetin Basoz
Reputation: 23797
Your trigger is inserting a series of values from Inserted table and you are saying PersonalIdentityNumber is the primary key in LogSeamen. Then it is obvious that, it would be violated on each update, because it is already inserted there on first insert operation. Remove PK_LogSeamen, or better yet create another auto generated column for the PK.
Upvotes: 1
Alex
Reputation: 5157
RE:
Violation of Primary key constraint 'PK_logSeamen'. Cannot insert duplicate key in object 'dbo.logSeamen'. The statement has been terminated.
Looks like you have an update trigger on Seamen table. Check what your trigger is doing and fix if necessary.
Upvotes: 1
Related Questions
- SQL Server add auto increment primary key to existing table
- The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
- Auto increment primary key in SQL Server Management Studio 2012
- Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
- INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server
- Foreign Key to non-primary key
- Primary key violation constraint
- Violation of PRIMARY KEY constraint