SQL Server "key column information is insufficient or incorrect. Update affects multiple lines "

environment : Windows Server 2003 sp2,sql Server 2000, software for managing fixed assets.

Failure phenomena:

To increase the fixed asset information for two Cisco switches at the same time, the serial number is different because the model is identical. So I want to finish one, can directly copy a document to change the inconsistent place on the line. But the front desk can't be realized. In the lazy and learning attitude, kill into the background, directly open the database operation. Go to the primary key limit and copy it with the following statement:

INSERT into Da111select *from da111where (scodel = ' 03-ne-076 ')

After copying, locate the newly copied row in the results list, and manually modify the primary key ID to make the two statements different. At this point, as long as the mouse clicks somewhere other than this line, the key column information is insufficient or incorrect. The update affects multiple lines, as follows:

650) this.width=650; "title=" 3.JPG "alt=" wkiol1hg3nosx-rlaaamjodutgq650.jpg "src=" http://s2.51cto.com/wyfs02/M00/ 8c/0f/wkiol1hg3nosx-rlaaamjodutgq650.jpg "/>

The crash is, whether it is re-loaded back to the original ID value (restore to two lines exactly the same), or delete the duplicate with DELETE statement, will pop up as warning, only from the upper right corner of the entire Close Query Analyzer window, in order to do other things.

Cause Analysis:

Whether you use the statement, or the mouse directly in the table to select the changes, in fact, are update. For a database, this is equivalent to modifying two identical rows, which is not allowed for SQL Server. So it would be like a hint.

Would it be possible for Oracle to allow seemingly identical lines to be modified because of the presence of ROWID? Test it later.

Workaround:

Know the reason, the solution is easy to find.

Method 1. Delete all duplicate lines and do it again manually! Note that you need to close the window to reopen the Query Analyzer window to run the statement:

Delete from Da111_bak2--can also be directly with the delete da111_bak2, without adding fromwhere (Scodel = ' 03-ne-076 ')

When checking for table dependencies, you can check them using the following window. However, you can only check the process, triggers, and so on, if it is referenced by another table, it seems to not be checked out, so this method is both cumbersome and unsafe.

650) this.width=650; "style=" Float:none; "title=" 1.JPG "alt=" wkiom1hh0mecb4c7aaayehuocyu426.jpg-wh_50 "src="/HTTP/ S4.51cto.com/wyfs02/m02/8c/18/wkiom1hh0mecb4c7aaayehuocyu426.jpg-wh_500x0-wm_3-wmp_4-s_48906402.jpg "/>

650) this.width=650; "style=" Float:none; "title=" snap1.jpg "alt=" wkiol1hh0mfrgfpwaab2zkfiniq717.jpg-wh_50 "src=" Http://s4.51cto.com/wyfs02/M02/8C/14/wKioL1hh0mfRgfPwAAB2zkfiNiQ717.jpg-wh_500x0-wm_3-wmp_4-s_1728970313.jpg "/ >

Method 3. Delete the primary KEY constraint in the table, copy the row, and then modify the ID of one of the rows in the way that the limit affects the row number, and then restore the PRIMARY KEY constraint. This method is very good, recommended .

--Query constraint name and delete constraint exec sp_helpconstraint ' DA111_BAK2 ' ALTER TABLE da111_bak2dropconstraint pk_da111_bak2--copy line insert Da111_ Bak2select *from da111_bak2where (scodel = ' 03-ne-076 ')/* Set the number of rows affected, copied once, set to only 1, if you have replicated multiple n rows repeatedly, and if you want to keep only one row in the end is not affected, set to n-1*/ Set ROWCOUNT to update 1 of these lines, changing the ID value. Update Da111_bak2set id= ' 14824553714603914625651755433750 '--set a different value where (Scodel = ' 03-ne-076 ')--restore settings set ROWCOUNT 0- -Restore PRIMARY KEY constraint ALTER TABLE Da111_bak2addconstraint pk_da111_bak2primary key (ID)

Reference: http://blog.163.com/m13864039250_1/blog/static/21386524820133155536227/

Http://blog.sina.com.cn/s/blog_415b73d101000838.html

SQL Server "key column information is insufficient or incorrect. Update affects multiple lines "