sql - 使用外键从现有数据库更改身份

Question

我犯了2个错误，我真的不知道如何解决它。起初，我在表中插入了一个对象，但身份不是很好。它应该是 1 而不是 10

我的第二个错误是我将表中的一些标识设置为 1,1 而不是 10,10。但由于我的两个错误都是外键，我真的不知道如何解决这个问题。

谢谢你。

PS我想知道如何更改这些身份，而不必在其他所有表中更改它，因为它是外键。

标签： sqlsql-serverdatabase

Answer 1

我认为您不是第一个决定使用外键等更改此类内容的人。

我通常的方法是非常手动的，但往往会奏效。我喜欢对这类情况进行大量的手动监督，而不是编写复杂的代码并希望它能正常工作。

对于下面的演示，我假设表 T1 有一个指向表 T2 的外键链接（例如，表 T1 有一个字段 T2_Id）。

主要问题是您不能只更改 T2 中的值（例如，从 1 到 10），因为它在表 T2 中不存在。第二个问题是您的表中可能已经存在所需的 ID。

因此，我采取的广泛方法是

1. 将 T2 中的行从原始 ID 复制到新 ID
2. 更新 T1 中的相关行以引用新的 T2_Id
3. 从 T2 中删除原始行

笔记

• 在 try-catch 和 transaction 中进行这些更改，以防出现任何问题
• 我发现先用 T2_ID_old 和 T2_ID_new 创建一个临时表很有用
• 您需要SET IDENTITY INSERT T2 ON;在插入 T2 之前，按照它SET IDENTITY INSERT T2 OFF;
• 如果您想要的值已经存在，那么您可能需要执行循环。最简单（尽管最慢）的循环是一次执行 1 个，并且（如果您要向上更改值）首先从最大值开始（以清除该点以供以后更新）

具有一次 1 行循环的代码的广泛示例。随时更新它。也可以随意使用 CURSOR 而不是 WHILE 循环。

DECLARE @CurrentT2IdOriginal int
DECLARE @CurrentT2IdNew int

SET @CurrentT2IdOriginal = (SELECT TOP 1 T2_ID_Original FROM #T2_Updates ORDER BY T2_ID_original DESC)

WHILE @CurrentT2IdOriginal IS NOT NULL
    BEGIN

    BEGIN TRY
        BEGIN TRANSACTION

        SET @CurrentT2IDNew = (SELECT TOP 1 T2_ID_New FROM #T2_Updates WHERE T2_ID_original = @CurrentT2IdOriginal)

        SET IDENTITY_INSERT T2 ON;
        INSERT INTO T2 (ID, randomtext)
            SELECT  @CurrentT2IdNew, randomtext
            FROM    T2
            WHERE   T2.ID = @CurrentT2IdOriginal
        SET IDENTITY_INSERT T2 OFF;

        UPDATE T1 SET T2_ID = @CurrentT2IdNew WHERE T2_ID = @CurrentT2IdOriginal

        DELETE FROM T2 WHERE Id = @CurrentT2IdOriginal

        SET @CurrentT2IdOriginal = (SELECT TOP 1 T2_ID_Original FROM #T2_Updates WHERE T2_ID_Original < @CurrentT2IdOriginal ORDER BY T2_ID_original DESC)

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH

    END

这是一个带有完整示例的DB<>fiddle

注意 - 在此过程中的某个时刻，您还需要将 ID 字段从 identity(1,1) 更改为 identity(10,10)。

重要的

• 首先在测试站点上执行此操作。
• 之后检查您的数据，以防有人在您执行此操作时插入了更多行。