الخميس، 11 يونيو، 2015

On delete cascade & On update cascade SQL Server Tips

Simple example to illustrate to amazing features in SQL server "On delete cascade & On update cascade"

Building Sample Data

USE [TestDB]
GO
-- Creating Table Products
CREATE TABLE [dbo].[Products](
[ProductID] [int] NOT NULL,
[ProductDesc] [varchar](50) NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)) ON [PRIMARY]
GO
-- Creating Table ProductDetails
CREATE TABLE [dbo].[ProductDetails](
[ProductDetailID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Total] [int] NOT NULL,
CONSTRAINT [PK_ProductDetails] PRIMARY KEY CLUSTERED
(
[ProductDetailID] ASC
)) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductDetails] WITH CHECK ADD CONSTRAINT [FK_ProductDetails_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
-- Insert Data into Table
USE TestDB
GO
INSERT INTO Products (ProductID, ProductDesc)
SELECT 1, 'Bike'
UNION ALL
SELECT 2, 'Car'
UNION ALL
SELECT 3, 'Books'
GO
INSERT INTO ProductDetails
([ProductDetailID],[ProductID],[Total])
SELECT 1, 1, 200
UNION ALL
SELECT 2, 1, 100
UNION ALL
SELECT 3, 1, 111
UNION ALL
SELECT 4, 2, 200
UNION ALL
SELECT 5, 3, 100
UNION ALL
SELECT 6, 3, 100
UNION ALL
SELECT 7, 3, 200
GO

Select Data from Tables

-- Selecting Data
SELECT *
FROM Products
SELECT *
FROM ProductDetails
GO

cascadedelete1 SQL SERVER   Curious Case of Disappearing Rows   ON UPDATE CASCADE and ON DELETE CASCADE   T SQL Example   Part 2 of 2

Delete Data from Products Table

-- Deleting Data
DELETE
FROM
Products
WHERE ProductID = 1
GO

Select Data from Tables Again

-- Selecting Data
SELECT *
FROM Products
SELECT *
FROM ProductDetails
GO

cascadedelete2 SQL SERVER   Curious Case of Disappearing Rows   ON UPDATE CASCADE and ON DELETE CASCADE   T SQL Example   Part 2 of 2

Clean up Data

-- Clean up
DROP TABLE ProductDetails
DROP TABLE Products
GO

ليست هناك تعليقات:

إرسال تعليق