حذف جزئیات مربوط به جدول اصلی [بایگانی]

Reza_Yarahmadi

شنبه 12 اسفند 1391, 07:58 صبح

دوست عزیز سوالی که بدلیل تکراری بودن حذف شده دوباره می پرسید؟
یه مقدار به خودتون زحمت بدید و جستجو کنید!
لینک 1 (http://barnamenevis.org/showthread.php?352427-%C3%99%C2%88%C3%9B%C2%8C%C3%98%C2%B1%C3%98%C2%A7%C 3%9B%C2%8C%C3%98%C2%B4-%C3%9B%C2%8C%C3%9A%C2%A9-%C3%99%C2%81%C3%9B%C2%8C%C3%99%C2%84%C3%98%C2%AF-%C3%99%C2%88-%C3%98%C2%AA%C3%98%C2%A7%C3%98%C2%AB%C3%9B%C2%8C%C 3%98%C2%B1-%C3%99%C2%87%C3%99%C2%85%C3%98%C2%B2%C3%99%C2%85%C 3%98%C2%A7%C3%99%C2%86-%C3%98%C2%A2%C3%99%C2%86-%C3%98%C2%A8%C3%98%C2%B1-%C3%98%C2%B1%C3%99%C2%88%C3%9B%C2%8C-%C3%99%C2%87%C3%99%C2%85%C3%98%C2%A7%C3%99%C2%86-%C3%99%C2%81%C3%9B%C2%8C%C3%99%C2%84%C3%98%C2%AF-%C3%98%C2%AF%C3%98%C2%B1-%C3%98%C2%A8%C3%98%C2%A7%C3%99%C2%82%C3%9B%C2%8C-%C3%98%C2%AA%C3%9B%C2%8C%C3%98%C2%A8%C3%99%C2%87%C 3%98%C2%A7)
لینک 2 (http://barnamenevis.org/showthread.php?326032-%C3%9B%C2%8C%C3%9A%C2%A9-%C3%98%C2%A7%C3%98%C2%A8%C3%99%C2%87%C3%98%C2%A7%C 3%99%C2%85-%C3%98%C2%AF%C3%98%C2%B1%C3%98%C2%A8%C3%98%C2%A7%C 3%98%C2%B1%C3%99%C2%87-%C3%98%C2%A7%C3%98%C2%B1%C3%98%C2%AA%C3%98%C2%A8%C 3%98%C2%A7%C3%98%C2%B7-%C3%98%C2%A8%C3%9B%C2%8C%C3%99%C2%86-%C3%99%C2%81%C3%9B%C2%8C%C3%99%C2%84%C3%98%C2%AF%C 3%99%C2%87%C3%98%C2%A7%C3%9B%C2%8C-%C3%98%C2%AC%C3%98%C2%AF%C3%98%C2%A7%C3%99%C2%88%C 3%99%C2%84-%C3%98%C2%A8%C3%98%C2%B1%C3%99%C2%86%C3%98%C2%A7%C 3%99%C2%85%C3%99%C2%87)
لینک 3 (http://barnamenevis.org/showthread.php?326192-%C3%99%C2%86%C3%99%C2%88%C3%98%C2%B4%C3%98%C2%AA%C 3%99%C2%86-2-%C3%98%C2%AF%C3%98%C2%B3%C3%98%C2%AA%C3%99%C2%88%C 3%98%C2%B1-%C3%98%C2%AD%C3%98%C2%B0%C3%99%C2%81-%C3%98%C2%AF%C3%98%C2%B1-%C3%9B%C2%8C%C3%9A%C2%A9-sp)
لینک 4 (http://barnamenevis.org/showthread.php?383022-%C3%98%C2%A7%C3%9A%C2%AF%C3%98%C2%B1-%C3%98%C2%AF%C3%99%C2%88-%C3%99%C2%81%C3%9B%C2%8C%C3%99%C2%84%C3%98%C2%AF-%C3%9A%C2%A9%C3%99%C2%84%C3%9B%C2%8C%C3%98%C2%AF-%C3%98%C2%A7%C3%98%C2%B5%C3%99%C2%84%C3%9B%C2%8C-%C3%98%C2%B1%C3%98%C2%A7-%C3%98%C2%AA%C3%98%C2%B4%C3%9A%C2%A9%C3%9B%C2%8C%C 3%99%C2%84-%C3%98%C2%A8%C3%98%C2%AF%C3%99%C2%87%C3%99%C2%86%C 3%98%C2%AF%C3%98%C2%8C-%C3%99%C2%85%C3%9B%C2%8C-%C3%98%C2%AA%C3%99%C2%88%C3%98%C2%A7%C3%99%C2%86-%C3%99%C2%85%C3%99%C2%82%C3%98%C2%AF%C3%98%C2%A7%C 3%98%C2%B1-%C3%9A%C2%A9%C3%99%C2%84%C3%9B%C2%8C%C3%98%C2%AF-%C3%98%C2%A7%C3%98%C2%B5%C3%99%C2%84%C3%9B%C2%8C-%C3%98%C2%B1%C3%98%C2%A7-%C3%98%C2%AA%C3%98%C2%BA%C3%9B%C2%8C%C3%9B%C2%8C%C 3%98%C2%B1-%C3%98%C2%AF%C3%98%C2%A7%C3%98%C2%AF%C3%98%C2%9F)
لینک 5 (http://barnamenevis.org/showthread.php?376134-%C3%9A%C2%86%C3%9A%C2%AF%C3%99%C2%88%C3%99%C2%86%C 3%99%C2%87-%C3%98%C2%A7%C3%98%C2%B1%C3%98%C2%AA%C3%98%C2%A8%C 3%98%C2%A7%C3%98%C2%B7-%C3%98%C2%A8%C3%98%C2%B1%C3%99%C2%82%C3%98%C2%B1%C 3%98%C2%A7%C3%98%C2%B1-%C3%9A%C2%A9%C3%99%C2%86%C3%99%C2%85-%C3%98%C2%9F)
اینها نتیجه یک دقیقه جستجو بود.

3 جدول زیر با داده های نمونه رو در نظر بگیرید
CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Val] [nvarchar](50) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Table_2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Val] [nvarchar](50) NULL,
[Tbl1ID] [int] NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Table_2] WITH CHECK ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([Tbl1ID])
REFERENCES [dbo].[Table_1] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]

CREATE TABLE [dbo].[Table_3](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Val] [nvarchar](50) NULL,
[Tbl2ID] [int] NULL,
CONSTRAINT [PK_Table_3] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Table_3] WITH CHECK ADD CONSTRAINT [FK_Table_3_Table_2] FOREIGN KEY([Tbl2ID])
REFERENCES [dbo].[Table_2] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[Table_3] CHECK CONSTRAINT [FK_Table_3_Table_2]

Insert Into Table_1 Values ('reza')
Insert Into Table_1 Values ('ali')

Insert Into Table_2 Values ('Data 1', 1)
Insert Into Table_2 Values ('Data 2', 2)
Insert Into Table_2 Values ('Data 3', 2)
Insert Into Table_2 Values ('Data 4', 2)
Insert Into Table_2 Values ('Data 5', 1)
Insert Into Table_2 Values ('Data 6', 2)

Insert Into Table_3 Values ('Value 1', 1)
Insert Into Table_3 Values ('Value 2', 2)
Insert Into Table_3 Values ('Value 3', 3)
Insert Into Table_3 Values ('Value 4', 4)
Insert Into Table_3 Values ('Value 5', 5)
Insert Into Table_3 Values ('Value 6', 6)
Insert Into Table_3 Values ('Value 7', 1)
Insert Into Table_3 Values ('Value 8', 2)
Insert Into Table_3 Values ('Value 9', 3)
Insert Into Table_3 Values ('Value 10', 4)
Insert Into Table_3 Values ('Value 11', 5)

Select * from Table_1
Select * from Table_1 T1 Inner Join Table_2 T2 ON T1.ID = T2.Tbl1ID
Select * from Table_1 T1 Inner Join Table_2 T2 ON T1.ID = T2.Tbl1ID Inner Join Table_3 T3 ON T2.ID = T3.Tbl2ID

حالا وقتی دستور زیر رو اجرا میکنیم ببینید نتیجه چی میشه

Delete Table_1 Where ID = 1

Select * from Table_1
Select * from Table_1 T1 Inner Join Table_2 T2 ON T1.ID = T2.Tbl1ID
Select * from Table_1 T1 Inner Join Table_2 T2 ON T1.ID = T2.Tbl1ID Inner Join Table_3 T3 ON T2.ID = T3.Tbl2ID