Correcting database inconsistencies - identifier fields

Question

I inherited an SQL database (Microsoft?), Which was not very pristine in its original state. There are still very strange things in it that I'm trying to fix - one of them is inconsistent identities.

In the account table, each entry has a number called accountID, which is referenced in several other tables (notes, equipment, etc.). The problem is that the numbers (for some random reason) vary from about -100,000 to +2,000,000 when there are about 7,000 records.

Is there a good way to reuse their numbers when changing the corresponding numbers in other tables? I also have ColdFusion at my disposal, so any thing that works with SQL and / or I accept.

Answer 1

For surrogate keys, they should be meaningless, so if you really didn’t have a problem with the integrity of the database (for example, there were no correct definitions of foreign keys), or your identity was close to the maximum for your data type, I would leave them in rest and go after some other low hanging fruit, which will have a greater impact.

Answer 2

: "" - , "". OP , , , , . ? ? , , , .

Answer 3

(, , ), , . , . , . , . , , . - , .

Answer 4

, ( ), script. script , , , , , .

script : http://vyaskn.tripod.com/sql_server_search_and_replace.htm

, , UPDATE, , script ( ).

, wack. ​​ ? , , ? , , .

Answer 5

ColdFusion , , . , , script , . .

, ColdFusion, , . . ( @@IDENTITY MAX (accountID)) , , .

. , . , , .

, , , , .

Answer 6

.

ALTER TABLE accounts
ADD new_accountID int IDENTITY

ALTER TABLE notes
ADD new_accountID int

ALTER TABLE equipment
ADD new_accountID int

new_accountID .

UPDATE notes
SET new_accountID = accounts.new_accountID
FROM accounts
INNER JOIN notes ON (notes.accountID = accounts.accountID)

UPDATE equipment
SET new_accountID = accounts.new_accountID
FROM accounts
INNER JOIN equipment ON (equipment.accountID = accounts.accountID)

, new_accountID . .

• .
• UPDATE [table] SET accountID = new_accountID.
• .
• new_accountID , .