-- 创建主表
CREATE TABLE tParent
(
tParent_Col1 int NOT NULL CONSTRAINT pkParent PRIMARY KEY,
tParent_Col2 nvarchar(50) NOT NULL CONSTRAINT uktParent UNIQUE
)
INSERT INTO tParent VALUES (1, N'ParentRecord1')
INSERT INTO tParent VALUES (2, N'ParentRecord2')
INSERT INTO tParent VALUES (3, N'ParentRecord3')
SELECT * FROM tParent
-- ===========================================
-- 创建从表 1
CREATE TABLE tChild1
(
tChild1_Col1 int NOT NULL CONSTRAINT fkChild1Parent FOREIGN KEY REFERENCES tParent (tParent_Col1),
tChild1_Col2 nvarchar(50) NOT NULL
)
INSERT INTO tChild1 VALUES (2, N'Child1Record1')
INSERT INTO tChild1 VALUES (3, N'Child1Record2')
INSERT INTO tChild1 VALUES (1, N'Child1Record3')
INSERT INTO tChild1 VALUES (2, N'Child1Record4')
SELECT * FROM tChild1
-- ===========================================
-- 创建从表 2
CREATE TABLE tChild2
(
tChild2_Col1 int NOT NULL,
tChild2_Col2 nvarchar(50) NOT NULL CONSTRAINT fkChild2Parent FOREIGN KEY REFERENCES tParent (tParent_Col2)
)
INSERT INTO tChild2 VALUES (11, N'ParentRecord2')
INSERT INTO tChild2 VALUES (22, N'ParentRecord1')
INSERT INTO tChild2 VALUES (33, N'ParentRecord3')
INSERT INTO tChild2 VALUES (44, N'ParentRecord3')
SELECT * FROM tChild2
-- ===========================================
-- 生成删除从表外键约束的 T-SQL 语句
-- 1. MS SQL Server 2000
DECLARE @stmt nvarchar(4000)
SELECT @stmt = ISNULL(@stmt + CHAR(13) + CHAR(10), '')
+ 'ALTER TABLE ' + OBJECT_NAME(fkeyid)
+ ' DROP CONSTRAINT ' + OBJECT_NAME(constid)
FROM sysforeignkeys
WHERE rkeyid = OBJECT_ID(N'tParent', N'U')
-- -- 2. MS SQL Server 2005 / 2008
-- DECLARE @stmt nvarchar(max)
-- SELECT @stmt = ISNULL(@stmt + CHAR(13) + CHAR(10), '')
-- + 'ALTER TABLE ' + OBJECT_NAME(parent_object_id)
-- + ' DROP CONSTRAINT ' + OBJECT_NAME(constraint_object_id)
-- FROM sys.foreign_key_columns
-- WHERE referenced_object_id = OBJECT_ID(N'tParent', N'U')
-- PRINT @stmt
-- ===========================================
-- 执行生成的 T-SQL 语句
EXECUTE sp_executesql @stmt
-- ===========================================
-- 删除主表
DROP TABLE tParent
-- ===========================================
-- 测试
INSERT INTO tChild1 VALUES (9999, N'Child1Record9999')
INSERT INTO tChild2 VALUES (9999, N'Child2Record9999')
SELECT * FROM tChild1
SELECT * FROM tChild2
-- ===========================================