blocks|key|2023483|text|您不能更改主键列，除非您删除it..Any操作以更改其数据类型将导致以下错误。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2023484|对象'XXXX‘依赖于列'XXXX’。|blockquote|2023485|唯一的选择就是|2023486|1.删除主键|2023487|2.改变数据类型|2023488|3.重新创建主键|2023489|ALTER+TABLE+t1++
DROP+CONSTRAINT+PK__t1__3213E83F88CF144D;+++
GO++

alter+table+t1+
alter+column+id+varchar(10)+not+null

alter+table+t1+add+primary+key+(id)|code-block|syntax|javascript|2023490|从2012年起，有一个名为(DROP_EXISTING+=+ON)的子句使事情变得简单，通过在最后阶段删除聚集索引，并且在您的情况下使所有operations..But都可以使用旧索引，这个子句将无法工作。|offset|length|style|CODE|2023491|所以我建议|2023492|1.使用不同的名称创建具有所需模式和索引的新表|2023493|2.从旧表插入数据到新表|2023494|3.最后，在切换时插入积累的数据。|2023495|4.将表重命名为旧表名|2023496|这样你的停机时间就会更少|2023497|entityMap^0|0|0|0|0|0|0|0|D|K|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1C|8|@]|9|@]|A|$]]|$1|E|3|F|5|6|7|1D|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|1E|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|1F|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1G|8|@]|9|@]|A|$]]|$1|M|3|N|5|O|7|1H|8|@]|9|@]|A|$P|Q]]|$1|R|3|S|5|6|7|1I|8|@$T|1J|U|1K|V|W]]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1L|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1M|8|@]|9|@]|A|$]]|$1|11|3|12|5|6|7|1N|8|@]|9|@]|A|$]]|$1|13|3|14|5|6|7|1O|8|@]|9|@]|A|$]]|$1|15|3|16|5|6|7|1P|8|@]|9|@]|A|$]]|$1|17|3|18|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|19|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]]|1A|$]]

You can't change primary key column,unless you drop it..Any operations to change its data type will lead to below error..

<blockquote>
 The object 'XXXX' is dependent on column 'XXXX'.
</blockquote>

Only option is to 

1.Drop primary key 
2.change data type 
3.recreate primary key 

<pre><code>ALTER TABLE t1 
DROP CONSTRAINT PK__t1__3213E83F88CF144D; 
GO 

alter table t1 
alter column id varchar(10) not null

alter table t1 add primary key (id)
</code></pre>

From 2012,there is a clause called <code>(DROP_EXISTING = ON)</code> which makes things simple ,by dropping the clustered index at final stage and also keeping old index available for all operations..But in your case,this clause won't work..

So i recommend 

1.create new table with desired schema and indexes,with different name 
2.insert data from old table to new table 
3.finally at the time of switch ,insert data that got accumulated 
4.Rename the table to old table name

This way you might have less downtime

blocks|key|1544221|text|下面是我编写的一个脚本，用于帮助我们部署对主键列数据类型的更改。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1544222|这个脚本假设没有任何非主键约束(例如外键)取决于这个列。|1544223|它有一些安全检查，因为它被设计为部署到不同的服务器(dev、uat、live)，如果服务器上的表有所不同，则不会产生副作用。|1544224|我希望这能帮上忙。如果你在投票前发现有什么问题，请告诉我。我非常乐意更新脚本。|1544225|IF+EXISTS(SELECT+1+FROM+INFORMATION_SCHEMA.COLUMNS+C+WITH+(NOLOCK)+WHERE+C.TABLE_CATALOG+=+'<<DB>>'+AND+C.TABLE_SCHEMA+=+'dbo'+AND+C.TABLE_NAME+=+'<<Table>>'+
AND+C.COLUMN_NAME+=+'<<COLUMN>>'+AND+C.DATA_TYPE+=+'int')+--+<-+Additional+test+to+check+the+current+datatype+so+this+won't+make+unnecessary+or+wrong+updates
BEGIN
++++DECLARE+@pkName+VARCHAR(200);
++++SELECT+@pkName+=+pkRef.CONSTRAINT_NAME+FROM+INFORMATION_SCHEMA.TABLE_CONSTRAINTS+pkRef+WITH+(NOLOCK)
++++WHERE+pkRef.TABLE_CATALOG+=+'<<DB>>'+AND+pkRef.TABLE_SCHEMA+=+'dbo'+AND+TABLE_NAME+=+'<<Table>>'

++++IF(@pkName+IS+NOT+NULL)
++++BEGIN
++++++++--+Make+sure+the+primary+key+name+is+the+one+you+are+going+to+use+in+script+beyond+this+point.
++++++++IF(@pkName+!=+'<<PRIMARY+KEY+NAME>>')+
++++++++BEGIN
++++++++++++RAISERROR+('Unexpected+primary+key+name+-+The+primary+key+found+has+a+different+name+than+expected.+Please+update+the+script.',+16,+1);
++++++++++++RETURN;
++++++++END

++++++++ALTER+TABLE+dbo.<<Table>>
++++++++++++++DROP+CONSTRAINT+<<PRIMARY+KEY+NAME>>;+--+Note:+this+is+not+a+string+or+a+variable+(just+type+the+PK+name)
++++++++SELECT+'Dropped+existing+primary+key';
++++END

++++ALTER+TABLE+dbo.<<Table>>+ALTER+COLUMN+ID+BIGINT
++++SELECT+'Updated+column+type+to+big+int';

++++ALTER+TABLE+dbo.<<Table>>
++++++++++ADD+CONSTRAINT+<<PRIMARY+KEY+NAME>>+PRIMARY+KEY+CLUSTERED+(<<COLUMN>>);
++++SELECT+'Created+the+primary+key';
END
ELSE
BEGIN
++++SELECT+'No+change+required.';
END|code-block|syntax|javascript|1544226|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|R|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Below is a script I wrote to help us deploy a change to primary key column data type. 

This script assumes there aren't any non-primary key constraints (e.g. foreign keys) depending on this column.

It has a few safety checks as this was designed to be deployed to different servers (dev, uat, live) without creating side effects if the table was somehow different on a server.

I hope this helps someone. Please let me know if you find anything wrong before down-voting. I'm more than happy to update the script.

<pre><code>IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS C WITH (NOLOCK) WHERE C.TABLE_CATALOG = '&lt;&lt;DB&gt;&gt;' AND C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME = '&lt;&lt;Table&gt;&gt;' 
AND C.COLUMN_NAME = '&lt;&lt;COLUMN&gt;&gt;' AND C.DATA_TYPE = 'int') -- &lt;- Additional test to check the current datatype so this won't make unnecessary or wrong updates
BEGIN
 DECLARE @pkName VARCHAR(200);
 SELECT @pkName = pkRef.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkRef WITH (NOLOCK)
 WHERE pkRef.TABLE_CATALOG = '&lt;&lt;DB&gt;&gt;' AND pkRef.TABLE_SCHEMA = 'dbo' AND TABLE_NAME = '&lt;&lt;Table&gt;&gt;'

 IF(@pkName IS NOT NULL)
 BEGIN
 -- Make sure the primary key name is the one you are going to use in script beyond this point.
 IF(@pkName != '&lt;&lt;PRIMARY KEY NAME&gt;&gt;') 
 BEGIN
 RAISERROR ('Unexpected primary key name - The primary key found has a different name than expected. Please update the script.', 16, 1);
 RETURN;
 END

 ALTER TABLE dbo.&lt;&lt;Table&gt;&gt;
 DROP CONSTRAINT &lt;&lt;PRIMARY KEY NAME&gt;&gt;; -- Note: this is not a string or a variable (just type the PK name)
 SELECT 'Dropped existing primary key';
 END

 ALTER TABLE dbo.&lt;&lt;Table&gt;&gt; ALTER COLUMN ID BIGINT
 SELECT 'Updated column type to big int';

 ALTER TABLE dbo.&lt;&lt;Table&gt;&gt;
 ADD CONSTRAINT &lt;&lt;PRIMARY KEY NAME&gt;&gt; PRIMARY KEY CLUSTERED (&lt;&lt;COLUMN&gt;&gt;);
 SELECT 'Created the primary key';
END
ELSE
BEGIN
 SELECT 'No change required.';
END
</code></pre>

blocks|key|1544277|text|可以分三个步骤更改主键的日期类型。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1544278|步骤1+:-删除与主键关联的约束|1544279|ALTER+TABLE+table_name
+DROP+CONSTRAINT+constraint_name;|code-block|syntax|javascript|1544280|步骤2+:-将Primay键列更改为有效的主键数据类型|1544281|ALTER+TABLE++table_name
ALTER+COLUMN+pk_column_name+target_data_type(size)+not+null;|1544282|步骤3+:-使更改后的列再次使用主键|1544283|ALTER+TABLE+table_name
ADD+PRIMARY+KEY+(pk_column_name);|1544284|PS+:-|1544285|当您试图更改pk_column时，可以从错误消息中获取约束名称。|unordered-list-item|1544286|如果pk_column中已经有数据，请确保列的源数据类型和目标数据类型都可以用于现有数据。否则，还需要另外两个步骤才能将现有数据移动到临时列，然后执行这些步骤，并在检查和删除该临时列后返回该数据。|1544287|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|10|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|11|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|12|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|13|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|14|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|15|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|16|8|@]|9|@]|A|$]]|$1|S|3|T|5|U|7|17|8|@]|9|@]|A|$]]|$1|V|3|W|5|U|7|18|8|@]|9|@]|A|$]]|$1|X|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|Y|$]]

You can change the date type of the primary key in three steps

Step 1 :- Drop the constraint associated with the Primary key

<pre><code>ALTER TABLE table_name
 DROP CONSTRAINT constraint_name;
</code></pre>

Step 2 :- Alter the Primay key column to a valid primary key data type 

<pre><code>ALTER TABLE table_name
ALTER COLUMN pk_column_name target_data_type(size) not null;
</code></pre>

Step 3 :- Make the altered column primary key again

<pre><code>ALTER TABLE table_name
ADD PRIMARY KEY (pk_column_name);
</code></pre>

<hr>

PS :- 

<ul>
<li>You can get the Constraint name from the error message when you try to alter the 
 pk_column </li>
<li>If you already have data in the pk_column make sure the source and target data type of the column both can be used for the existing data. else another two steps would be needed to move the existing data to a temporary column and then perform the steps and bring back that data after vetting and dropping that temporary column.</li>
</ul>

<hr>

blocks|key|1544325|text|就在表中，您要更改PK类型的>>修改。进入列，更改类型并保存。如果您想查看这种更改的代码，在保存之前，可以右键单击>>“..”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1544326|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Right in the table you want to change the PK type >> Modify. Go in the column, change the type and save. If you want to see the code for such a change, before saving, you can right-click >> "Generate Change Script ..".

blocks|key|223656|text|如果其他表使用索引的FK引用您的PK，则必须遵循以下步骤。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|223657|在本例中，主表名为Main，即单个引用表Reference。我正在将数据类型更改为NVARCHAR(7)。使用它：|offset|length|style|CODE|223658|用自己的表名查找/替换所有这些表名；|unordered-list-item|223659|修改数据类型；|223660|您还可能需要单独查找/替换dbo模式；|223661|我使用包含约束名称的语法--如果需要，还可以将它们更新为首选的命名约定。|223662|ALTER+TABLE+dbo.Main+ADD+IdNew+NVARCHAR(7);
UPDATE+dbo.Main+SET+IdNew+=+Id;

--+For+all+tables+with+FK's+to+this+Main:

ALTER+TABLE+dbo.Reference+ADD+MainIdNew+NVARCHAR(7);
UPDATE+dbo.Reference+SET+MainIdNew+=+MainId;

ALTER+TABLE+dbo.Reference+DROP+CONSTRAINT+FK_Reference_MainId_Main_Id;
DROP+INDEX+IX_Reference_MainId+ON+dbo.Reference;
ALTER+TABLE+dbo.Reference+DROP+COLUMN+MainId;

--+Until+here

ALTER+TABLE+dbo.Main+DROP+CONSTRAINT+PK_Main;
ALTER+TABLE+dbo.Main+DROP+COLUMN+Id;

EXEC+sp_rename+'dbo.Main.IdNew',+'Id',+'COLUMN';
ALTER+TABLE+dbo.Main+ALTER+COLUMN+Id+NVARCHAR(7)+NOT+NULL;
ALTER+TABLE+dbo.Main+ADD+CONSTRAINT+PK_Main+PRIMARY+KEY+(Id);

--+Again+for+all+tables+with+FK's+to+this+Main:

EXEC+sp_rename+'dbo.Reference.MainIdNew',+'MainId',+'COLUMN';
ALTER+TABLE+dbo.Reference+ADD+CONSTRAINT+FK_Reference_MainId_Main_Id+FOREIGN+KEY+(MainId)+REFERENCES+dbo.Main(Id);
CREATE+INDEX+IX_Reference_MainId+ON+dbo.Reference(MainId);|code-block|syntax|javascript|223663|entityMap^0|0|9|4|K|9|15|B|0|0|0|D|3|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@$D|Z|E|10|F|G]|$D|11|E|12|F|G]|$D|13|E|14|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|15|8|@]|9|@]|A|$]]|$1|K|3|L|5|J|7|16|8|@]|9|@]|A|$]]|$1|M|3|N|5|J|7|17|8|@$D|18|E|19|F|G]]|9|@]|A|$]]|$1|O|3|P|5|J|7|1A|8|@]|9|@]|A|$]]|$1|Q|3|R|5|S|7|1B|8|@]|9|@]|A|$T|U]]|$1|V|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|W|$]]

In case other tables reference your PK with indexed FK's, these are the steps you must follow.
In this example, the main table's called <code>Main</code>, the single referencing table <code>Reference</code>. I'm changing the datatype to <code>NVARCHAR(7)</code>. To use it:
<ul>
<li>Find/replace all these table names with your own;</li>
<li>Modify the data type;</li>
<li>You might also need to separately find/replace the <code>dbo</code> schema;</li>
<li>I'm using syntax which includes constraint names - if you want, also update these to your preferred naming conventions.</li>
</ul>
<pre class="lang-sql prettyprint-override"><code>ALTER TABLE dbo.Main ADD IdNew NVARCHAR(7);
UPDATE dbo.Main SET IdNew = Id;

-- For all tables with FK's to this Main:

ALTER TABLE dbo.Reference ADD MainIdNew NVARCHAR(7);
UPDATE dbo.Reference SET MainIdNew = MainId;

ALTER TABLE dbo.Reference DROP CONSTRAINT FK_Reference_MainId_Main_Id;
DROP INDEX IX_Reference_MainId ON dbo.Reference;
ALTER TABLE dbo.Reference DROP COLUMN MainId;

-- Until here

ALTER TABLE dbo.Main DROP CONSTRAINT PK_Main;
ALTER TABLE dbo.Main DROP COLUMN Id;

EXEC sp_rename 'dbo.Main.IdNew', 'Id', 'COLUMN';
ALTER TABLE dbo.Main ALTER COLUMN Id NVARCHAR(7) NOT NULL;
ALTER TABLE dbo.Main ADD CONSTRAINT PK_Main PRIMARY KEY (Id);

-- Again for all tables with FK's to this Main:

EXEC sp_rename 'dbo.Reference.MainIdNew', 'MainId', 'COLUMN';
ALTER TABLE dbo.Reference ADD CONSTRAINT FK_Reference_MainId_Main_Id FOREIGN KEY (MainId) REFERENCES dbo.Main(Id);
CREATE INDEX IX_Reference_MainId ON dbo.Reference(MainId);
</code></pre>

blocks|key|1544379|text|使用Microsoft+SQL+Server+Management+Studio执行以下操作：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1544380|开放式表Design|ordered-list-item|1544381|更改主键列类型或通过这种方式也可以进行的任何其他更改|1544382|右键单击设计区域并选择Generate+Change+Script|1544383|接受Validation+Warning|1544384|预览更改或将其保存在文件中。|1544385|利润:)|1544386|这适用于对表的任何更改，只需记住SSMS会创建一个临时的第二个表来执行像主列类型更改这样的困难更改。|1544387|这在18.9版的应用程序中适用于我。|1544388|​|1544389|📷|atomic|1544390|1544391|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/24b4f208725c2b0afc87837cc68e3372.png|imageAlt^0|2|12|0|4|6|0|0|B|M|0|2|I|0|0|0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|1B|8|@$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|1E|8|@$9|1F|A|1G|B|C]]|D|@]|E|$]]|$1|I|3|J|5|H|7|1H|8|@]|D|@]|E|$]]|$1|K|3|L|5|H|7|1I|8|@$9|1J|A|1K|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|1L|8|@$9|1M|A|1N|B|C]]|D|@]|E|$]]|$1|O|3|P|5|H|7|1O|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|1P|8|@]|D|@]|E|$]]|$1|S|3|T|5|6|7|1Q|8|@]|D|@]|E|$]]|$1|U|3|V|5|6|7|1R|8|@]|D|@]|E|$]]|$1|W|3|X|5|6|7|1S|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|10|7|1T|8|@]|D|@$9|1U|A|1V|1|1W]]|E|$]]|$1|11|3|X|5|6|7|1X|8|@]|D|@]|E|$]]|$1|12|3|-4|5|6|7|1Y|8|@]|D|@]|E|$]]]|13|$14|$5|15|16|17|E|$18|19|1A|-4]]]]

Using <code>Microsoft SQL Server Management Studio</code> do the following:
<ol>
<li>Open table <code>Design</code></li>
<li>Change the primary key column type or any other change which is also possible with this way</li>
<li>Right click on the design area and select <code>Generate Change Script</code></li>
<li>Accept <code>Validation Warning</code></li>
<li>Preview changes or save them in file.</li>
<li>Profit :)</li>
</ol>
This works for any change to the table, just bare in mind that SSMS creates a temporary second table to do the difficult changes like primary column type change.
This works for me in version 18.9 of the app.
<a href="https://i.stack.imgur.com/J0c1h.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/J0c1h.png" alt="enter image description here" /></a>

I am working on <code>SQL Server 2012</code>:

I have a table with a primary key column as <code>INT</code>. I need to change this to a GUID.

Do I alter the table and remove int column as <code>primary key</code>?

Add the GUID column and set it as Primary and drop the old INT column?

Thank you.

SQL Server Change Primary Key Data Type

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我在做SQL Server 2012我有一个表，其主键列为INT。我需要将它更改为GUID。我是否修改表并将int列移除为primary key添加GUID列并将其设置为主列并删除旧的INT列？谢谢。

问Server更改主键数据类型
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Server更改主键数据类型EN