修改具有外键关系约束的字段
项目中用到一个表，主键为ID，数据类型为varchar（4），但是数据多了varchar的长度就不够了，现要将这个字段增加下长度，无奈的是这个字段是此表的主键，同时也具有很多表引用的外键，所以要修改他的长度就不好改了；
1、在设计视图中修改还是比较方便的，弹出如下图片的窗口，选择是就行了
2、在设计视图中修改虽然方便但是难免会需要打开SqlServer Management Studio去找到对应的表去修改，现在项目中领导要的是SQL脚本，没办法，琢磨琢磨吧……
琢磨大半天终于想出来一种方法，就是先把所有跟这个表有关的外键全部记录下来包括记录表名、外键列名，然后全部删掉，再来修改对应的列的字段属性，比如改为varchar（10），都改完之后再去修改主表里的ID属性为varchar（10），然后在重新加回来所有有关的外键关系，虽然有点小麻烦，不过还是解决了问题，代码如下：
--1、创建临时表用以存储Code表的所有外键关系的信息（包括：外键表名、外键列名、字段长度）
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'ForeignKeyInfo')
DROP TABLE ForeignKeyInfo
CREATE TABLE [dbo].[ForeignKeyInfo](
[FTableName] [varchar](1000) NOT NULL, -- 外键表名
[FColName] [nvarchar](1000) NOT NULL, -- 外键列名
[FColLength] [varchar](1000) NOT NULL, -- 外键列长度
[FName] [varchar](1000) NOT NULL, -- 外键名称
)
INSERT INTO [dbo].[ForeignKeyInfo] SELECT OBJECT_NAME(f.fkeyid) AS FTableName,col.name AS FColName,col.length AS FColLength,OBJECT_NAME(f.constid) AS FName
FROM sysforeignkeys f, syscolumns col
WHERE OBJECT_NAME(f.rkeyid)='Code' AND f.fkeyid=col.id AND f.fkey=col.colid
--SELECT * FROM ForeignKeyInfo
--2、循环删除Code表的所有外键关系
DECLARE @Loop INT; -- 循环索引
DECLARE @FKeyCount INT; -- 外键数量
DECLARE @FTableName VARCHAR(100); -- 要删除外键的表名
DECLARE @FNameToDelete VARCHAR(1000); -- 要删除的外键名
DECLARE @ColName VARCHAR(100); --要修改长度的列
DECLARE @EditLenStr VARCHAR(1000); --修改长度命令
DECLARE @DeleteFKeyStr VARCHAR(8000); -- 删除外键关系的命令
DECLARE @IsColNull int;--判断字段是否可为空Null
SELECT @FKeyCount = COUNT(*) FROM ForeignKeyInfo
SET @Loop=1;
WHILE @Loop <= @FKeyCount
BEGIN
SET @DeleteFKeyStr=''
SELECT @FTableName=FTableName,@FNameToDelete=FName,@ColName=FColName FROM ( SELECT *,ROW_NUMBER() Over(ORDER BY FName ) AS rowId FROM ForeignKeyInfo) t WHERE rowid = @Loop
SELECT @DeleteFKeyStr='ALTER TABLE [dbo].['+@FTableName+'] DROP CONSTRAINT ['+@FNameToDelete+'];'
SELECT @EditLenStr='ALTER TABLE [dbo].['+@FTableName+'] ALTER COLUMN ['+@ColName+'] VARCHAR(10)'
--判断该字段是否可为空
SELECT @IsColNull=c.IsNullable FROM syscolumns c
INNER JOIN systypes t ON c.xusertype = t.xusertype
LEFT JOIN sys.extended_properties ETP ON ETP.major_id = c.id AND ETP.minor_id = c.colid AND ETP.name ='MS_Description'
LEFT JOIN syscomments CM ON c.cdefault=CM.id
WHERE c.id = object_id(@FTableName) AND c.name=@ColName;
IF @IsColNull=0--不可为空null
BEGIN
SET @EditLenStr=@EditLenStr+' NOT NULL';
END
SET @EditLenStr=@EditLenStr+';';
EXEC(@DeleteFKeyStr);--删除外键
EXEC(@EditLenStr) --修改字段长度
SET @Loop=@Loop+1;
END
--3、修改Code表ID字段的长度
ALTER TABLE Code DROP CONSTRAINT [PK_CODE];--修改前要删除主键约束
ALTER TABLE Code ALTER COLUMN ID VARCHAR(10) NOT NULL;
ALTER TABLE Code ADD CONSTRAINT [PK_CODE] PRIMARY KEY(ID);
--重新建立外键关系
DECLARE @AddFkeyStr VARCHAR(8000)
SET @Loop=1
WHILE @Loop<=@FKeyCount
BEGIN
SELECT @FTableName=FTableName,@FNameToDelete=FName,@ColName=FColName FROM ( SELECT *,ROW_NUMBER() Over(ORDER BY FName ) AS rowId FROM ForeignKeyInfo) t WHERE rowid = @Loop
SELECT @AddFkeyStr='ALTER TABLE ['+@FTableName+'] WITH CHECK ADD CONSTRAINT ['+@FNameToDelete+'] FOREIGN KEY(['+@ColName+']) REFERENCES [dbo].[Code] ([ID]);'
EXEC(@AddFkeyStr);
SET @Loop=@Loop+1;
END
--删除临时表
DROP TABLE [dbo].[ForeignKeyInfo]
到这里就算完活了，收工了，当然可能还有别的办法，不过暂时先不去想了，先休息下，哈哈！
