TableAdapters : DataTable ConstraintException Was Unhandled - Column is Constrained to be Unique - Value is Present
by David Hayden ( Florida .NET Developer )
Last week was a busy week for me getting ready for the release of a new customer website that went live today with glorious success :)
During the project I was using the new TableAdapters and typed DataSets at times to help with a bit of rapid application development! While using the TableAdapters and typed DataSets, I came across a ConstraintException Error I have seen reported in the MSDN Forums that I thought had to do with a misconfiguration of a SQL Server Table. Turns out that is not the case.
ConstraintException Was Unhandled - Column is Constrained to be Unique - Value is Present
The error happens when inserting records into a database table and might say something like:
“ConstraintException was unhandled. Column 'CustomerId' is constrained to be unique. Value '1' is already present.”
One might think SQL Server is returning this error, but it is actually the DataTable that throws the exception.
Customers Example
Let's take the example of a brand new table in SQL Server, called Customers. For simplicity, let's say we have 3 columns in the table, one being the PK CustomerId, which is an identity with an initial seed of 1 and an increment of 1.
We drag and drop the Customers Table on a new CustomersDataSet Item and watch the VS 2005 IDE gloriously create a typed CustomersDataTable and CustomersTableAdapter for us as such:
Adding Records
We now want to populate this Customers Database Table with a few records as such:
CustomersDataTable customers = new CustomersDataTable();
customers.AddCustomersRow("John", "Doe");
customers.AddCustomersRow("Jane", "Doe");
customers.AddCustomersRow("Jack", "Frost");
customers.AddCustomersRow("Mother", "Nature");
CustomersTableAdapter adapter = new CustomersTableAdapter();
adapter.Update(customers);
Right before the update, the in-memory CustomersDataTable looks like this:
When we run the code, we run into the following exception, thrown not by SQL Server, but by our CustomersDataTable:
Looking at the Insert Query
You can better understand why the ConstraintException is being thrown by looking at the InsertCommand of the CustomersTableAdapter, which looks like this:
INSERT INTO [dbo].[Customers] ([FirstName], [LastName])
VALUES (@FirstName, @LastName);
SELECT CustomerId, FirstName, LastName FROM Customers
WHERE (CustomerId = SCOPE_IDENTITY())
There are actually two SQL statements being sent to SQL Server. The first one inserts the record, the second one retrieves all the values in the newly inserted row to populate the CustomersDataTable.
The CustomerId PK, Scope_Identity(), will be '1' for the first record. But wait a minute, '1' already exists in the second row of the in-memory CustomersDataTable. This will be a problem, because this column is supposed to be unique. Just as the CustomersTableAdapter inserts the actual PK, '1', into the DataTable for the first record, we get the following exception:
“ConstraintException was unhandled. Column 'CustomerId' is constrained to be unique. Value '1' is already present.”
This isn't a big deal to fix, but it is an extra step that certainly could have been taken care of by the VS2005 IDE in the first place.
Fixing the ConstraintException Problem
The answer to this problem is simple. Change the AutoIncrementSeed and AutoIncrementStep Values on the CustomersDataTable to -1 and -1.
When we change the AutoIncrementSeed and AutoIncrementStep, the CustomersDataTable looks like such before the update:
These PK values should never cause a collision with values generated in the SQL Server Table.
Conclusion
Although I don't expect this ConstraintException problem to be an issue is most cases, hopefully this will help those who may have been stuck by the problem.
Recent ADO.NET Tutorials
Source: David Hayden ( Florida .NET Developer )
Filed: ADO.NET Tutorials