Cannot delete a row in a table in an SQL Server 2

[maupiti]

SQL 2000 SERVER

In SQL SERVER Enterprise Manager.

I have a table, and this table has no primary key
or index key defined, and it does not have any relationship
with other table. When I try to delete a row it gives me an
error.

Error: Key column information is insufficient
or incorrect. Too many rows were affected by update.

 

[unclejimbob]

I don't suppose this KB applies (pk not defined because of potentially repeating info) ?

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

What happens when you use QA ?

cheers

 

[bborissov]

This is normal behaviour of SQL Server. Becuase you don't have any PK there is no way SQL Server to identify which record you want to delete. If you have several rows with equal info in them SQL server doesn't "know" which of them you want to delete. That's why you get this error message. If you want to delete ALL rows with the sqm info in them you must use DELETE ... WHERE .... clause. That way you will delete them (no metter if there is only one row with that info). My advice is always to put some column that will identify the row uniquely. One identity field could do that job.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.