主页 / dba / 问题 / 128433
Accepted
Hikari
Asked: 2016-02-06 11:03:46 +0800 CST

将自动增量添加到现有 PK

  • 772

我在另一个数据库中已经存在的数据库中创建了一个表。它最初是用旧的数据库数据填充的。表的 P​​K 必须接收那些记录上已经存在的值，所以它不能是自动增量的。

现在我需要新表将其 PK 作为自动增量。但是在 PK 已经存在并且有数据之后，我该怎么做呢？

sql-server primary-key

5 个回答

  1. Best Answer

    我理解你的问题的方式是你有一个现有的表，其中有一列到目前为止已经填充了手动值，现在你想要 (1) 使这个列成为一个IDENTITY列，并且 (2) 确保IDENTITY开始从现有行中的最新值。

    首先，一些测试数据可以使用：

    CREATE TABLE dbo.ident_test (
    id    int NOT NULL,
    xyz   varchar(10) NOT NULL,
    CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id)
);

INSERT INTO dbo.ident_test (id, xyz)
VALUES (1, 'test'),
       (2, 'test'),
       (5, 'test'),
       (6, 'test'),
       (10, 'test'),
       (18, 'test'),
       (19, 'test'),
       (20, 'test');

    目标是使表的主键列 ,id成为一个IDENTITY从 21 开始的列，用于插入下一条记录。对于此示例，该列xyz表示该表的所有其他列。

    在你做任何事情之前，请阅读这篇文章底部的警告。

    首先，万一出现问题：

    BEGIN TRANSACTION;

    现在，让我们添加一个临时工作列，id_temp并将该列设置为现有id列的值：

    ALTER TABLE dbo.ident_test ADD id_temp int NULL;
UPDATE dbo.ident_test SET id_temp=id;

    接下来，我们需要删除现有id列（您不能只是“添加”IDENTITY到现有列，您必须将列创建为IDENTITY）。主键也必须去，因为列依赖它。

    ALTER TABLE dbo.ident_test DROP CONSTRAINT PK_ident_test;
ALTER TABLE dbo.ident_test DROP COLUMN id;

    ...并再次添加该列，这次作为IDENTITY，以及主键：

    ALTER TABLE dbo.ident_test ADD id int IDENTITY(1, 1) NOT NULL;
ALTER TABLE dbo.ident_test ADD CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id);

    这就是有趣的地方。您可以IDENTITY_INSERT在表上启用，这意味着您可以IDENTITY在插入新行时手动定义列的值（但不更新现有行）。

    SET IDENTITY_INSERT dbo.ident_test ON;

    使用该设置，DELETE表中的所有行，但您要删除的行都在OUTPUT同一个表中 - 但列具有特定值id（来自备份列）。

    DELETE FROM dbo.ident_test
OUTPUT deleted.id_temp AS id, deleted.xyz
INTO dbo.ident_test (id, xyz);

    一次，完成，IDENTITY_INSERT再次关闭。

    SET IDENTITY_INSERT dbo.ident_test OFF;

    删除我们添加的临时列：

    ALTER TABLE dbo.ident_test DROP COLUMN id_temp;

    最后，为列重新设定种子IDENTITY，因此下一条记录id将在列中现有的最高数字之后恢复id：

    DECLARE @maxid int;
SELECT @maxid=MAX(id) FROM dbo.ident_test;
DBCC CHECKIDENT ("dbo.ident_test", RESEED, @maxid)

    检查示例表，最高id数字为 20。

    SELECT * FROM dbo.ident_test;

    添加另一行并检查其新行IDENTITY：

    INSERT INTO dbo.ident_test (xyz) VALUES ('New row');
SELECT * FROM dbo.ident_test;

    在示例中，新行将具有id=21. 最后，如果您满意，请提交事务：

    COMMIT TRANSACTION;

    重要的

    这不是一个微不足道的操作，它带有很多你应该注意的风险。

    • 在专用的测试环境中执行此操作。有备份。:)

    • 我喜欢使用它，BEGIN/COMMIT TRANSACTION因为它可以防止其他进程在您更改表格时弄乱表格，并且如果出现问题，它可以让您回滚所有内容。但是，在您提交事务之前尝试访问您的表的任何其他进程最终都会等待。如果您有一张大桌子和/或您在生产环境中，这可能会非常糟糕。

    • OUTPUT .. INTO如果您的目标表具有外键约束或我不记得的许多其他功能中的任何一个，则将无法使用。您可以改为将数据卸载到临时表中，然后将其重新插入原始表中。您也许可以使用分区切换（即使您不使用分区）。

    • 一个接一个地运行这些语句，而不是作为批处理或在存储过程中。

    • 尝试考虑可能取决于id您要删除和重新创建的列的其他事情。必须删除并重新创建任何索引（就像我们对主键所做的那样）。请记住为您需要预先重新创建的每个索引和约束编写脚本。

    • 禁用表上的任何INSERT和DELETE触发器。

    如果重新创建表是一个选项：

    如果您可以选择重新创建表，那么一切都会简单得多：

    • 创建空表，id列为IDENTITY,
    • IDENTITY_INSERT ON为餐桌设置，
    • 填充表格，
    • 设置IDENTITY_INSERT OFF, 和
    • 重新设定身份。
    • 17

  2. 使用 UPDATE、DELETE 或 INSERT 移动数据可能会花费大量时间并使用数据和日志文件/磁盘上的资源 (IO)。在处理大表时，可以避免用可能的大量记录填充事务日志：使用分区切换，仅更改元数据。

    不涉及数据移动，因此执行速度非常快（几乎是瞬时的）。

    样品表

    该问题未显示原始表 DDL。以下 DDL 将用作此答案中的示例：

    CREATE TABLE dbo.idT(
    id int not null
    , uid uniqueidentifier not null
    , name varchar(50)
);
ALTER TABLE dbo.idT ADD CONSTRAINT PK_idT PRIMARY KEY CLUSTERED(id);

    使用此查询添加了从 0 到 15 的六个虚拟随机 ID：

    WITH ids(n) AS(
    SELECT x1.n+x2.n*4
    FROM (values(0), (3)) as x1(n)
    CROSS JOIN (values(0), (2), (3)) as x2(n)
)
INSERT INTO idt(id, uid, name)
SELECT n, NEWID(), NEWID() 
FROM ids

    示例数据IdT

    id  uid                                     name
0   65533096-5007-43EA-88AD-D6776B3B94FA    6A69D4F2-D682-4168-A92F-4CD2E2DBC21D
3   CE87F1ED-BE1A-4F2D-8D62-E1ECA822D35B    AF0524D9-0DBB-41E1-883B-003CB4E4F012
8   34A1DBFD-4F92-4F34-9F04-4CDC824AB15A    02B4BDA4-D515-4262-9031-0BE496AC24CE
11  51606C95-9DE8-4C30-B23B-F915EEA41156    93258103-9C22-4F9C-85CF-712ED0FB3CE6
12  CEC80431-0513-4751-A250-0EB3390DACAB    2DA6B8AF-3EBC-42B3-A76C-028716E24661
15  5037EA83-286F-4EBC-AD7C-E237B570C1FF    095E51E9-8C38-4104-858F-D14AA810A550

    新表与IDENTITY(0, 1)

    唯一的问题idT是缺少IDENTITY(0, 1)id 上的属性。一个具有类似结构的新表IDENTITY(0, 1)被创建：

    CREATE TABLE dbo.idT_Switch(
    id int identity(0, 1) not null
    , uid uniqueidentifier not null
    , name varchar(50)
);
ALTER TABLE dbo.idT_Switch ADD CONSTRAINT PK_idT_Switch PRIMARY KEY CLUSTERED(id);

    除了IDENTITY(0, 1)，idT_Switch与 相同idT。

    外键

    必须删除外键idT才能使用此技术。

    分区开关

    idT和idT_Switch表具有兼容的结构。除了使用DELETE,UPDATE和INSERT语句将行从自身移动idT到自身idT_Switch或在idT自身之上，ALTER TABLE ... SWITCH可以使用：

    ALTER TABLE dbo.idT
SWITCH TO dbo.idT_Switch;

    PK_idT（整个表）的单个“分区”被移动到PK_idT_Switch（反之亦然）。idT现在包含 0 行并idT_Switch包含 6 行。

    您可以在此处找到源和目标兼容性要求的完整列表：

    使用分区切换高效传输数据

    请注意，这种使用SWITCH不需要企业版，因为没有显式分区。从 SQL Server 2005 开始，未分区表被认为是具有单个分区的表。

    代替idT

    idT现在是空的和无用的，可以丢弃：

    DROP TABLE idT;

    idT_Switch可以重命名并将替换旧idT表：

    EXECUTE sys.sp_rename
    @objname = N'dbo.idT_Switch',
    @newname = N'idT', -- note lack of schema prefix
    @objtype = 'OBJECT';

    外键

    外键可以再次添加到新idT表中。以前为了使表兼容切换而删除的任何其他内容idT也需要重做。

    补种

    SELECT IDENT_CURRENT( 'dbo.idT');

    此命令返回 0。表 idT 包含 6 行，MAX(id) = 15。可以使用DBCC CHECKIDENT (table_name) ：

    DBCC CHECKIDENT ('dbo.idT');

    因为 15 比 0 大，所以它会自动重新播种而不寻找 MAX(id)：

    如果表的当前标识值小于标识列中存储的最大标识值，则使用标识列中的最大值对其进行重置。请参阅下面的“例外”部分。

    IDENT_CURRENT 现在返回15。

    测试并添加数据

    一个简单的INSERT声明：

    INSERT INTO idT(uid, name) SELECT NEWID(), NEWID();

    添加这一行：

    id  uid                                     name
16  B395D692-5D7B-4DFA-9971-A1497B8357A1    FF210D9E-4027-479C-B5D8-057E77FAF378

    该id列现在使用标识，新插入的值确实是 16 (15+1)。

    更多信息

    这里有一个相关的问题和答案，其中包含有关该SWITCH技术的更多背景：

    为什么不支持删除列上的 Identity 属性

    • 5

  3. 如果你想从一个新的身份值开始，你需要重新设定你的身份。查看文档CHECKIDENT

    DBCC CHECKIDENT (yourtable, reseed, starting point)
    • 4

  4. 启用和禁用 IDENTITY_INSERT

    如果您的表是 TABLE_A 那么

    1. CREATE TABLE TABLE_B 类似于具有标识列的 TABLE_A
    2. 设置 IDENTITY_INSERT TABLE_B ON
    3. 从 TABLE_A 插入到 TABLE_B
    4. 设置 IDENTITY_INSERT TABLE_B 关闭
    5. DROP TABLE TABLE_A 并重命名表 B Exec sp_rename 'TABLE_B', 'TABLE_A'
    • 0

  5. 如果您有像我这样的情况，那么您可以更轻松地完成此任务

    第 1 步：获取一组好的数据，包括键字段值（在我的情况下，问题是主键无法与其他两个表正确同步）

    第 2 步：使用 SSMS 功能创建您需要创建的表的删除和创建表。此外，请确保 NEW 表添加了主标识键

    注意：
    这对我来说是必要的，因为我使用带有主键种子的好数据，这是正确的主键递增，但我不再能够自动递增该字段

    步骤 3：使用以下查询插入 PK 的相同增量

    • INSERT INTO [dbo].[fact_table2]
    ([dim_order_id]
    ,[dim_inventory_id]
    ,[primary_location_velocity_code]
    ,[profit_ordered]
    ,[fill_rate]
    ,[profit_unfilled]
    ,[dollar_weighted_fill_rate]
    ,[missed_sales])

 SELECT [dim_order_id]
    ,[dim_inventory_id]
    ,[primary_location_velocity_code]
    ,[profit_ordered]
    ,[fill_rate]
    ,[profit_unfilled]
    ,[dollar_weighted_fill_rate]
    ,[missed_sales]
    from
    [dbo].[fact_table1]
    order by
    [fact_table1_id] asc

    第 4 步：测试表是否相同

    • select top 10 *
from [dbo].[fact_table1]
where
profit_unfilled > 0
   order by
   [fact_table1_id] desc

    • select top 10 *
from [dbo].[fact_table2]
where
profit_unfilled > 0
   order by
   [fact_table1_id] desc

    最后一步：请注意，插入语句没有使用 PK 字段....它仅用于对插入的数据进行正确排序:)

    • 0

相关问题