CASCADE allows deletions or updates of key values to cascade through the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed. ON DELETE CASCADE specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. ON UPDATE CASCADE specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key.
Let me reproduce the simple scenario in T-SQL.
-- Creating
Table Products
CREATE TABLE [dbo].[Products]
(
[ProductID] [int] NOT NULL PRIMARY KEY CLUSTERED,
[ProductDesc] [varchar](50) NOT NULL
)
GO
-- Creating
Table ProductDetails
CREATE TABLE [dbo].[ProductDetails]
(
[ProductDetailID] [int] NOT NULL PRIMARY KEY CLUSTERED,
[ProductID] [int] NOT NULL
CONSTRAINT
[FK_ProductDetails_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
ON UPDATE CASCADE
ON DELETE CASCADE,
[Total] [int] NOT NULL
)
GO
-- Insert Data
into Table
INSERT INTO Products (ProductID,
ProductDesc)
VALUES (1, 'Bike'),(2, 'Car'),(3, 'Books')
GO
INSERT INTO ProductDetails ([ProductDetailID],[ProductID],[Total])
VALUES (1, 1, 200),(2, 1, 100),(3, 1, 111),(4, 2, 200),(5, 3, 100),
(6, 3, 100),(7, 3, 200)
GO
--Select Data
from Tables
SELECT *
FROM Products
SELECT *
FROM
ProductDetails
GO
On Delete:
--Delete Data
from Products Table
DELETE
FROM Products
WHERE ProductID = 1
GO
--Select Data
from Tables Again
SELECT *
FROM Products
SELECT *
FROM
ProductDetails
GO
On Update:
--Update Data
from Tables
UPDATE Products
SET ProductID = 5
WHERE ProductID = 3
GO
--Select Data
from Tables Again
SELECT *
FROM Products
SELECT *
FROM
ProductDetails
GO
No comments:
Post a Comment