SQLServer 有物理主外键关系的表在主表改变主键索引时的测试

--先试删除外键，避免后面如删除主表异常IF OBJECT_ID('FK_MainId') IS NOT NULLBEGINalter table Sub_Table drop constraint FK_MainIdEND--删除主表再建IF OBJECT_ID('Main_Table') IS NOT NULLBEGINDROP TABLE Main_TableENDGOCREATE TABLE Main_Table(MainId INT,MainName NVARCHAR(10),CONSTRAINT PK_Main_Table PRIMARY KEY(MainId ASC))GO--删除子表再建IF OBJECT_ID('Sub_Table') IS NOT NULLBEGINDROP TABLE Sub_TableENDGOCREATE TABLE Sub_Table(SubId INT,MainId INT,MainName NVARCHAR(10),CONSTRAINT PK_Sub_Table PRIMARY KEY(SubId ASC),CONSTRAINT FK_MainId FOREIGN KEY (MainId) REFERENCES Main_Table( MainId ))GO--插入主表数据INSERT INTO Main_Table (MainId,MainName) VALUES(1,'小明')INSERT INTO Main_Table (MainId,MainName) VALUES(2,'小华')--插入子表数据INSERT INTO Sub_Table (SubId,MainId,MainName) VALUES(10,1,'吃饭')INSERT INTO Sub_Table (SubId,MainId,MainName) VALUES(11,1,'睡觉')INSERT INTO Sub_Table (SubId,MainId,MainName) VALUES(12,1,'洗衣服')INSERT INTO Sub_Table (SubId,MainId,MainName) VALUES(20,2,'吃好饭')INSERT INTO Sub_Table (SubId,MainId,MainName) VALUES(21,2,'睡好觉')INSERT INTO Sub_Table (SubId,MainId,MainName) VALUES(22,2,'洗好衣服')--输入数据SELECT * FROM Main_Table mtSELECT * FROM Sub_Table st--修改主表主键索引EXEC sp_rename 'PK_Main_Table','PK_Main_Table2'--修改列名类型测试ALTER TABLE Main_Table ALTER COLUMN MainId BIGINT