Column 'opOrderID' is constrained to be unique. Value xxxx is already present.

Question

_{Wednesday, August 26, 2009 1:53 PM}

I get this exception where I least expeccted it. Also, idiotically, I got it first time either this morning or yesterday although I've run this code for months. I make a lot of changes and apparently remote ones somehow influence this event.

This is why I least expect the exception. I am not adding any new row with the same OrderID. I don't touch the column value at tall. Instead I modify other fields in this tow and finally say: table.AcceptChanges ( ); This is whare I get hit.

It defies logic and I have no idea what to think of it.

Thanks.AlexB

All replies (15)

_{Wednesday, August 26, 2009 2:19 PM ✅Answered}

You can't make a default to a primary key as String.Empty, set the AutoNumber

//Set up the ID column as the primary key [Just an example]
productsTable.PrimaryKey = new DataColumn() {productsTable.Columns["ID"]};
productsTable.Columns["ID"].AutoIncrement = true;
productsTable.Columns["ID"].AutoIncrementSeed = 1;
productsTable.Columns["ID"].ReadOnly = true;  

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

_{Wednesday, August 26, 2009 2:02 PM}

Is it an actual value or null/empty?  When records are created on the database the ID column is, for a brief moment, empty/null until the insert transaction completes and the ID is generated.

I have seen when creating records an exception occurs in the insert and the record is created but the ID is not generated and thus left null/empty and the next insert throws that kind of exception since there is a record in the database with a null/empty ID when the server tried to insert a new record. See if all your ID's are valid.

_{Wednesday, August 26, 2009 2:12 PM}

Thank you Michael. The default value for the opOrderID (a Primary Key) is string.Empty. Nulls are not allowed, naturally. When the record is created, even if this field is empty it does not hurt since it is the only record with empty field (not null) in the table. Fairly soon, but not immediately. sometimes with 2 second interval or a bit longer this field is filled. Until it is done no new row can be added of course. When this particular exception takes place this field is already filled with a meaningful value. It is a nine digit char string.

AlexB

_{Wednesday, August 26, 2009 2:27 PM}

It will change the entire ballgame. Why can't I do it? Could you explain? Currrently it is a character string giving it autoincrement will necessitate making the type Integer. I rely on the fact that it is empty for some time and check the value periodically. If it still empty I branch the logic accordingly. Could you provide a reason for your recommendation?

Thanks.AlexB

_{Wednesday, August 26, 2009 2:29 PM}

Just seems to me that you would violate the restraint. And it already appears you have, hence your post.

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

_{Wednesday, August 26, 2009 2:33 PM}

Alex,

Gotta agree with these folks.  It's not the best practice to use a string as your primary key.  The issue comes in where you'll have to come up with a unique value each time you insert anything into the database.  String.Empty or not, if the column is supposed to be unique, and you've got more than one string.Empty in there, then you're going to get this error. 

I guess I'd ask this question: is there any specific reason you are using the string as the primary key?  Is the value meaningful in a larger business sense?  (Sometimes, this is the case.  I could see a case for making a stock symbol a primary key, as they should all be unique), but in 90% of the situations, this isn't what you should be doing. 

Changing the value to an integer and allowing autoincrement will allow the database to take care of setting this value every time without you having to think about it. 

I'd recommend not changing the existing column, but instead, adding a primary key, and making that the unique column. Thus, the rest of your logic can still stay in place.

Coding Light - Illuminated Ideas and Algorithms in Software
Coding Light Wiki • Twitter • LinkedIn • ForumsBrowser

_{Wednesday, August 26, 2009 2:33 PM}

But empty string is not null. I can put a crazy character in there to identify empty, native, untouched  field of course.AlexB

_{Wednesday, August 26, 2009 2:36 PM}

A composite primary key or a string valued one may make sense in the Database world at times, but it makes for a lousy disconnected layer in the ADO.NET world.John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

_{Wednesday, August 26, 2009 2:38 PM | 1 vote}

Having a unique string value as a key to represent an order ID or what not is okay but restrict those keys to regular type indices/constraints and enforce them in code. We use all surrogate integers primary keys and let the database do the grunt work,but also have unique string values in the same record representing interpretable/meaningful ID values for users.

_{Wednesday, August 26, 2009 2:38 PM}

But empty string is not null. I can put a crazy character in there to identify empty, native, untouched  field of course.

---

AlexB

Exactly, but you're going to have to make sure that it's a different crazy character each and every time.  It's a unique field, so every value that goes into the field has to be unique. 

At the very least, you remove the constraint in your datatable, and make absolutely sure you have a unique value in each and every row before you submit to the database.  Be sure to check the database also to make sure that items that don't exist in the dataset are still unique compared to the database.  That's alot of work. 

Change to an autoincrementing integer, and you're done.  Make it bigint, you won't run out of possible rows in your lifetime.  I guarantee it. Coding Light - Illuminated Ideas and Algorithms in Software
Coding Light Wiki • Twitter • LinkedIn • ForumsBrowser

_{Wednesday, August 26, 2009 2:40 PM}

Not to mention if you ever want to instantiate a fail-over or replication scheme you will need integer keys ( in some cases guids which the server can do seamlessly ).

_{Wednesday, August 26, 2009 2:42 PM}

I did set the column default value to "*" to remove any doubt and controversy. It will work as well.

Thank you for the comment.AlexB

_{Wednesday, August 26, 2009 2:45 PM}

Not to mention if you ever want to instantiate a fail-over or replication scheme you will need integer keys ( in some cases guids which the server can do seamlessly ).

Michael, this is an internal .NEt set up. I do store the net result in Sql Server but it goes on seemlessly. This table is unlikely to have more than a dozen rows.

AlexB

_{Wednesday, August 26, 2009 2:46 PM}

Not to mention that querying for an Int32 or even a BigInt will make the efficacy of searches faster then having to parse through and look for a string value.John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

_{Wednesday, August 26, 2009 2:52 PM}

Not to mention that querying for an Int32 or even a BigInt will make the efficacy of searches faster then having to parse through and look for a string value.

---

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

John, this particular table is very small, not in terms of the number of coulmns (22) but in tems of the rows it can acquire. When all this troubl began the table had only one row, partially filled.AlexB