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

--先试删除外键，避免后面如删除主表异常
IF OBJECT_ID('FK_MainId') IS NOT NULL
BEGIN
	alter table Sub_Table drop constraint FK_MainId
END
--删除主表再建
IF OBJECT_ID('Main_Table') IS NOT NULL
BEGIN
	DROP TABLE Main_Table
END
GO
CREATE TABLE Main_Table(
	MainId INT,
	MainName NVARCHAR(10),
	CONSTRAINT PK_Main_Table PRIMARY KEY(
		MainId ASC	
	)	
)
GO
--删除子表再建
IF OBJECT_ID('Sub_Table') IS NOT NULL
BEGIN
	DROP TABLE Sub_Table
END
GO
CREATE 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 mt
SELECT * FROM Sub_Table st

--修改主表主键索引
EXEC sp_rename 'PK_Main_Table','PK_Main_Table2'

--修改列名类型测试
ALTER TABLE Main_Table ALTER COLUMN MainId BIGINT