依赖表上的ALTER TABLE [英] ALTER TABLE on dependent column

问题描述

我试图将主键的列数据类型从int更改为tinyint.此列是其他表中的外键.因此，出现以下错误:

I am trying to alter column datatype of a primary key to tinyint from int.This column is a foreign key in other tables.So,I get the following error:

消息5074，级别16，状态1，第1行对象'PK_User_tbl'是 取决于列"appId".消息5074，第16级，状态1，第1行 对象"FK_Details_tbl_User_tbl"取决于列"appId".讯息 5074，Level 16，State 1，Line 1对象'FK_Log_tbl_User_tbl'为 取决于列"appId".讯息4922，第16级，状态9，第1行，更改 TABLE ALTER COLUMN appId失败，因为一个或多个对象访问 此列.

Msg 5074, Level 16, State 1, Line 1 The object 'PK_User_tbl' is dependent on column 'appId'. Msg 5074, Level 16, State 1, Line 1 The object 'FK_Details_tbl_User_tbl' is dependent on column 'appId'. Msg 5074, Level 16, State 1, Line 1 The object 'FK_Log_tbl_User_tbl' is dependent on column 'appId'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN appId failed because one or more objects access this column.

---

除了删除依赖关系并重新创建依赖关系之外，还有其他方法吗?

---

Is there any other way other than to delete dependencies and recreate them?

推荐答案

我相信您将必须首先删除外键约束.然后更新所有适当的表并按原样重新映射它们.

I believe that you will have to drop the foreign key constraints first. Then update all of the appropriate tables and remap them as they were.

ALTER TABLE [dbo.Details_tbl] DROP CONSTRAINT [FK_Details_tbl_User_tbl];
-- Perform more appropriate alters
ALTER TABLE [dbo.Details_tbl] ADD FOREIGN KEY (FK_Details_tbl_User_tbl) 
    REFERENCES User_tbl(appId);
-- Perform all appropriate alters to bring the key constraints back

但是，除非内存是一个大问题，否则我将身份保留为INT.除非您100％肯定您的钥匙永远不会超过TINYINT约束.请注意:)

However, unless memory is a really big issue, I would keep the identity as an INT. Unless you are 100% positive that your keys will never grow past the TINYINT restraints. Just a word of caution :)

这篇关于依赖表上的ALTER TABLE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！