Constraint ON UPDATE,DELETE CASCADE -- guzel ornek
Definition Description
http://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/ use deneme go -- Table creation logic --parent table CREATE TABLE [dbo].[Order]( [OrderID] [bigint] NOT NULL, [OrderData] [varchar]( 10 ) NOT NULL, CONSTRAINT [PK_Order_1] PRIMARY KEY CLUSTERED ( [OrderID] ASC ) ) GO
-- child table CREATE TABLE [dbo].[OrderDetail]( [OrderDetailID] [bigint] NOT NULL, [OrderID] [bigint] NULL, [OrderData] [varchar]( 10 ) NULL, CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED ( [OrderDetailID] ASC ) ) GO -- foreign key constraint ALTER TABLE [dbo].[OrderDetail] WITH CHECK ADD CONSTRAINT [FK_OrderDetail_Order] FOREIGN KEY( [OrderID] ) REFERENCES [dbo].[Order] ( [OrderID] ) ON DELETE CASCADE GO
-- data load DECLARE @val BIGINT DECLARE @val2 BIGINT
SELECT @val = 1 WHILE @val < 100000 BEGIN PRINT @val INSERT INTO dbo.[Order] VALUES( @val, 'TEST' + CAST( @val AS VARCHAR )) SELECT @val2 = 1
WHILE @val2 < 20 BEGIN PRINT @val2 INSERT INTO dbo.[OrderDetail] VALUES (( @val*100000 )+ @val2, @val,'TEST' + CAST( @val AS VARCHAR )) SELECT @val2 = @val2 + 1 END SELECT @val = @val + 1 END GO /* First Example Now that we have some data, let's remove a record from the [Order] table. Here is the code. Note: I've added a "DBCC DROPCLEANBUFFERS" to each DML query to ensure there is no data in the cache before we run the statement. */ DBCC DROPCLEANBUFFERS GO DELETE FROM [Order] WHERE OrderID=24433 GO
SELECT * FROM OrderDetail WHERE OrderID = 24433 -- Kayıt yok çünkü on delete cascade orderdetial üzerinde açıktı /* After running this statement we can query the [OrderDetail] table and verify that the records were removed. To get an idea of what needs to be done to if we did not have the DELETE CASCADE option set let's remove it and see what happens. Here is the code: */ ALTER TABLE [dbo].[OrderDetail] DROP CONSTRAINT [FK_OrderDetail_Order] GO ALTER TABLE [dbo].[OrderDetail] WITH CHECK ADD CONSTRAINT [FK_OrderDetail_Order] FOREIGN KEY([OrderID]) REFERENCES [dbo].[Order] ([OrderID]) GO /* Now let's run the following code, remembering that we have to delete the records from [OrderDetail] first as DELETE CASCADE option has been removed from the foreign key. You can see how the previous example is much simpler as it only requires one DELETE statement. Imagine if we had 5 or 6 tables with foreign keys into this parent table. To remove the parent record we would have to have a separate delete statement for each of these child tables. */ DBCC DROPCLEANBUFFERS GO DELETE FROM [OrderDetail] WHERE OrderID=24032 DELETE FROM [Order] WHERE OrderID=24032 GO /* Let's also take a look a the performance differences of these two approaches. Below is a SQL Profiler output from both DELETE scenarios. You can see from this trace using the DELETE CASCADE option also uses less resources than doing the DELETE with a separate statement. Aşağıda delete cascade ile ve delete cascade olmadan yapılan silme işlemlerinin performans karşılaştırması mevcuttur. delete cascade ile yapılan silme işlemi daha hızlı olmaktadır. */ DELETE CASCADE CPU (ms) Reads Writes Duration Yes 281 12323 2 950 No 374 24909 3 1162
