sql-server-2008 - 对依赖列进行 ALTER TABLE

Question

我正在尝试将主键的列数据类型从 int 更改为tinyint。此列是其他表中的外键。因此，我收到以下错误:

<小时/>

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.

<小时/>

除了删除依赖项并重新创建它们之外，还有其他方法吗？

Answer 1

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

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% 确信您的 key 永远不会超出 TINYINT 限制。请注意:)