MS SQL: Key column information is insufficient...
 
While updating a test table in SQL Server Express 2005 I received this error in SQL*XL:
Key column information is insufficient or incorrect. Too many rows were affected by update.
 
A short investigation into this problem.
 
Database: SQL Server Express 2005
Driver: Microsoft OLE DB Provider for SQL Server (SQLOLEDB)
 
I created this table: create table test (a numeric, b date);
I created some test data executing 15 times: insert into test (1, null);
 
Using SQL*XL in Excel I queried the table: select * from mytable.
I changed some values in column a and tried to run the multiple rows update. It errors with the message:
Key column information is insufficient or incorrect. Too many rows were affected by update.
 
I tried to do the same thing in Microsoft SQL Server Management Studio Express. Changing values in column a the tool errors:
The data in row 8 was not committed.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (15 rows).

 
It seems it hits the same underlying problem. I bet this has to do with the absence of a key column. In Microsoft SQL Server Management Studio Express I set a primary key on column a (after emptying the table). Now I can change values in the columns. However when I by accident use a value that has already been used I get an error. Logical since there is a primary key on the field.
 
Trying views:
creating a view on this table with create view vw as select * from test rendered to an updatable view. Through the multiple rows update feature in SQL*XL data can be modified in both columns.
 
Now a more complex scenario. I created a new table with the same columns as test but without the primary key. I called it test2. Now I create a view on these tables joining them by column a:
create view vw2 as select test.a a, test.b b1, test2.b b2 from test, test2 where test.a = test2.a
 
Trying to do an update on the results of select * from vw2 failed with this message:
Insufficient key column information for updating or refreshing.
 
Actually it fails when I try to update column b2 (this column is based on test2.b). When I update b1 (based on test.b) or a (based on test.a) I do not get any errors. It seems it is related to table test2 not having a primary key.  
 
Adding a primary key to table test2 using server manager actually allows me to update all columns! However... inspecting the underlying tables test and test2 it seems only column a in table test has been changed! Running select * from vw2 indeed shows that the just updated row is not in the view anymore. Condition test.a = test2.a does not hold anymore. This is not very good although I can understand why it happens.