Strange Behaviour in Table That Has No Key, No Rowguid

[JohnBates]

Hi SQL Servers,

I'm working with a SQL Server 7 table that someone else created.

In Enterprise Manager, I tried to delete 2 rows from this table....got an error I've never seen before....
"Key column information is insufficient or incorrect. Too many rows were affected by the update." Red X

My SQL Select statement only returned 2 rows... so it's strange that is says "too many rows affected..."

Also, I've noticed that when I insert just a few rows, the row count Enterprise Mgr shows is wrong - shows more rows than it should.

** For whatever reason, the developer did not flag any columns as Key and none are Identity, none are Rowguid. **

Could that cause this problem? and what should I do?

Thanks, John

 

[Snowcrash]

You might like to check what triggers have been placed on this table. To do this, right click on the table name and choose all Tasks/Manage Triggers from the pop up menu. From teh screen you are then provided with, go to the dropdown list at the top left corner and click the down arrow to see if any triggers have been created. If any are there, check for your error message amongst the code.

Barb

 

[JohnBates]

Thanks for the suggestion Snowcrash.

There are no triggers on this table.

 

[JohnBates]

oops... just to give the situation accurately,

this is SQL Server 2000, not 7

John

 

[MeanGreen]

I found this link that might explain what is happening to you:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q268505&

Hope this helps.

 

[Transcend]

MeanGreen is right,

I've had the same problem, so I used query analyser to run a delete query to get around it.

Transcend

 

[JohnBates]

Thanks MeanGreen and Transcend,

I WAS able to delete these 2 rows by doing it in the Query Analyzer window just as you sugggested. The Microsoft page describes this problem as one that was in both Ver 7 and still did not get fixed in version 2000 - not good.

I will add a uniqueidentifier (newid()) column to this table to enforce uniqueness for every row.

Thanks for your tips. John

 

[SQLSister]

If they knew it was a problem in 7.0 and didn't fix it 2000, then it's not a bug, it's a feature!

 

[harryrich]

There are a number of update situations both in ODBC and in trigger processing where things just don't work out if a table lacks a unique constaint. Probably the only way to prevent all of them would be for each table to have have a hidden identifier column.

Meanwhile, it is probably best practice to give each table a designed identifier column (such as an identity or rowguid) tagged as a unique constraint or index. In addition to making things easier for SQL Server, and therefore whoever is working with it, it makes a number of application issues easier to deal with.

One place where this practice has been a little bit difficult is doing a bulk import of delimiter separated text data. Since you won't be importing the identifier, it would seem that you would need to define all the columns but the identifier column in an import specification. The work-around is to construct a view of the table including exactly the columns you with to import and bulk-import into the view.

Best,

Harry