更改巨大表PK列的数据类型 [英] Change huge table PK column data type

问题描述

现在我们的PK列（这是 IDENTITY ）的 int 容量已用完想要对 bigint 执行此操作，但是简单的 ALTER TABLE 似乎无法处理这么大的表。所以我的问题是：如何在保持实际值不变的情况下更改PK列的类型，还需要更改引用表吗？

Now that we've ran out of int capacity on a PK column (which is an IDENTITY) I'd like to do this to bigint, but simple ALTER TABLE seems to be unable to handle that big of a table. So my question is: how do I change the type of a PK column with keeping actual values in place and do I need to alter referencing tables as well?

推荐答案

除了KLE的建议外，以下查询可能会有所帮助：

In addition to KLE's suggestion, the following queries might help:

要禁用引用oldTable的表上的所有约束，请尝试执行以下命令的输出以下查询：

To disable all constraints on the tables that reference oldTable try to execute the output of the following query:

SELECT 'ALTER TABLE ' + OBJECT_NAME(fk.parent_object_id) + ' NOCHECK CONSTRAINT ' + fk.name
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns AS fkc ON fk.OBJECT_ID = fkc.constraint_object_id
WHERE OBJECT_NAME (fk.referenced_object_id) = 'oldTable'

要将所有数据移动到新表中，并更改字段，请尝试以下操作：

To move all data into the new table, with alteration of the field try this:

INSERT INTO newTable
SELECT CONVERT(BIGINT, ID) AS ID, COL1, COL2, ..., COLN
FROM oldTable

要删除旧表：

DROP TABLE oldTable

将新表重命名为旧名称：

To rename the new table to the old name:

sp_rename newTable, oldTable

要重新启用引用oldTable的表上的所有约束，请尝试执行以下查询的输出：

To reenable all the constraints on the tables that reference oldTable, try to execute the output of the following query:

SELECT 'ALTER TABLE ' + OBJECT_NAME(fk.parent_object_id) + ' CHECK CONSTRAINT ' + fk.name
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns AS fkc ON fk.OBJECT_ID = fkc.constraint_object_id
WHERE OBJECT_NAME (fk.referenced_object_id) = 'oldTable'

希望对您有帮助...

Hope it helps...

这篇关于更改巨大表PK列的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！