Enterprise Manager Error when trying to delete columns

Question

Enterprise Manager Error when trying to delete columns

marcopolo56

SSChasing Mays

Points: 653
More actions
June 2, 2004 at 7:48 am

#84590

I get this error when I try to delete or update some columns in a table.
"Key column information is insufficient or incorrect. Too many rows were affected by update"
This table has relationship with 2 other tables (Foreign keys in the problem table) Primary in the other tables. Even when I take away the relationship I get this error. What is wrong?

OK I see it is wroking from the Query Analyser. Strange
June 7, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Usually I see this when I have a table that has two rows with the exact same data in every column that was defined with no uniqueness in any column. To get around I usually have to add and Identity column to make them unique (even thou they all may be being deleted). Then I can either remove the new column or leave for future need.

shahgols SSCrazy Eights Points: 9874 More actions · Answer 2

I second what Antares said. I see the error because there are two or more rows that have the info that you are deleting. So if you query a table and get the customer firstname, lastname and if you want to delete John Smith, chances are that there are many John Smiths in the table. Which one does Sql Server need to delete? You need to pull the primary key as well so that Sql Server knows what row it should delete.

Greg Myers-143565 Grasshopper Points: 23 More actions · Answer 3

SQL does not know which file to delete since the data in the rows is exactly the same. Duplicated data usually causes this error...A way to correct this error is to create a new table with the same structure and then copy the content over through Query Analyzer. Then you can delete the old table and rename the new one the exact name of the previous table........

Or you can clear the contents of the old table and copy everything back from the new table to the old...........

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

You should do as Antares already suggeted. And once you're done, define a proper PRIMARY KEY on that table, so that you don't run into the same issue again one fine day.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]