Shulou(Shulou.com)06/01 Report--

Environment: windows server 2003 sp2,sql server 2000, software for managing fixed assets.

Failure phenomenon:

To add fixed asset information for two Cisco switches at the same time, because everything is the same model, but the serial number is different. So I want to finish one, can I just copy a document to correct the inconsistencies? But the front desk can't do it. In the spirit of laziness and learning attitude, kill to the background, directly open the database operation. After removing the primary key restriction, copy it with the following statement:

INSERT INTO da111SELECT * FROM da111WHERE (scodel = '03MurneMel 076')

After copying, find the newly copied row in the result list, and manually modify the primary key ID directly to make the two statements different. At this point, as long as the mouse clicks somewhere other than this line, it prompts "the key column information is insufficient or incorrect." the update affects multiple rows, as follows:

What crashes is that whether you re-install and change the original ID value back to the original ID value (revert to the same two lines), or delete the duplicate with a delete statement, you will pop up a warning like the above, only if you close the query analyzer window from the upper right corner. To do anything else.

Cause analysis:

Whether you use a statement or use the mouse to select changes directly in the table, it is actually update. For the database, this is tantamount to modifying two identical rows, which is not allowed for SQL SERVER. So there will be a hint like this.

So is it possible for ORACLE to allow identical line changes because of the existence of ROWID? Test it later.

Solution:

If you know the reason, it is easy to find a solution.

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

DELETE FROM da111_bak2-you can also use delete da111_bak2 directly without adding fromWHERE (scodel = '03murneMel 076')

When checking table correlation, you can use the following window to check. However, you can only check procedures, triggers, and so on, and you can't seem to check them if they are referenced by other tables, so it's troublesome and insecure to use this method.

Method 3. First delete the primary key constraint in the table, then copy the row, then modify the ID of the row in a limited way that affects the number of rows, and then restore the primary key constraint. This is a great method. I recommend it.

-- query the name of the constraint and delete the number of rows affected by the constraint exec sp_helpconstraint 'da111_bak2'alter table da111_bak2dropconstraint pk_da111_bak2-- replication line INSERT da111_bak2SELECT * FROM da111_bak2WHERE (scodel =' 03murneMutual 076') / *. It is copied once, and only 1 is set. If you copy multiple times with a total of n rows, and you want to keep only one row unaffected, set it to n-1*/SET ROWCOUNT 1 and update one of them. Change the ID value. Update da111_bak2set id='14824553714603914625651755433750'-set a different value WHERE (scodel = '03murneMel 076')-restore setting SSET ROWCOUNT 0MB-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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.