101 SQL Server Reporting Services Interview Questions with Answers

see the below Link

http://www.sqlserver-training.com/101-sql-server-reporting-services-interview-questions-with-answers/-

Cascade deleting in Sql Server 2012

Hi,

  suppose we have create two tables with primary key and foreign key relation.then u try to delete a record from primary key table.then u got a foreign key violation error to overcome this problem  we use 

ON DELETE CASCADE   

 command .

If we remove a record in p.key table then the corresponding records in f.key table are alsodeleted.

 

 In the same way update is also done .if we update a record in p.key table then automatically updated in f.key table

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

delete from Products where ProductID=1
update Products set ProductID=5 where ProductID=3