>>>We're using MissingSchemaAction.AddWithKey and suddenly had Rows.Find blow up because the value passed didn't match the table PK constraint columns.
>>>
>>>I figured out what was happening because I realized that we just changed some of our indexes and it turned out we have a new clustered key that matches the columns ADO.NET thinks comprises a composite "PK" constraint. (This is one of those times where our primary key is not clustered -- trying to optimize our indexes).
>>>
>>>I've searched high and low for this behavior, and any work arounds... other than to just add code that sets the DataTable.PrimaryKeys to what we need at any given moment... or settings I've somehow missed.
>>>
>>>Seems strange, though, that ADO.NET would "mistake" the clustered key for the primary one... especially where it's using it to set a "primary" constraint?
>>>
>>>Am I missing something here? Maybe the answer's "out there" and I'm just using bad search text?
>>>
>>>Thanks for any help,
>>>---J
>>
>>Jason,
>>
>>This is actually a bug in the SQL ADO.NET provider. ADO.NET relies on the GetSchemaTable method to discover keys and constraints. If you want to see where the confusion comes from, just look at the help for this method :)
>>
>>P.S. Shame on you for clustering something other than your PK! Time to move those clustered keys to their own tables. Remember rule #3 - Eliminate columns not dependent on the key (and move them to a separate table).
>>
>>Either that or shoot the DBA.
>>
>>JUST JOKING!
>>
>>- Keith
>
>Shame on me too. Why should I always cluster PK? If I do that I also need a sequential PK,no?
>Cetin

Cetin,

You shouldn't *always* cluster your PK. See my response to Jason for the reasons. As far as a sequential key, it is not necessary either. The big benefit with a sequential key is that SQL Server is able to short-circuit the sort/insert operation that happens on the index when a new row is added to the table. Once the row is inserted and the index is syncronized, there is no more benefit to having a sequential key.