--先试删除外键,避免后面如删除主表异常
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