ÔÚ MS SQL Server ÖÐɾ³ý±í

-- ˵Ã÷:
-- 1. Ҫɾ³ý±í table_name, ÐëÏÈÅжϸñíÊÇ·ñÕý±»Êý¾Ý¿âÖÐÆäËü±íËùÒýÓÃ.
-- 2. Èç¹ûδ±»ÒýÓÃ, ¿ÉÖ±½Ó DROP TABLE table_name; ·ñÔò±ØÐëÏÈɾ³ýÒýÓñíµÄÔ¼Êø, ÔÙ DROP TABLE table_name.
-- 3. ÁíÍâ, table_name ÊÇ·ñÕýÒýÓÃÆäËü±íµÄÇé¿öÎÞÐ뿼ÂÇ.


-- ´´½¨Ö÷±í
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')

-- --