Tuesday, 10 January 2012

On Cascading deletes in SQL Server

CREATE TABLE USERS
(
    USR_ID int
    ,CONSTRAINT [PK_Temp_Users1] PRIMARY KEY CLUSTERED ([USR_ID])
)
CREATE TABLE USER_PHONE
(
    USR_ID int
    ,CONSTRAINT [PK_Temp_Users2] PRIMARY KEY CLUSTERED ([USR_ID])
)
ALTER TABLE [dbo].USER_PHONE  WITH CHECK ADD
CONSTRAINT [FK_Temp_UsersPhone_Users] FOREIGN KEY([USR_ID])
REFERENCES [dbo].[Users] ([USR_ID])
ON DELETE CASCADE
GO
INSERT INTO USERS
    SELECT 1 UNION SELECT 2 UNION SELECT 3
INSERT INTO USER_PHONE
    SELECT 1 UNION SELECT 2 UNION SELECT 3

SELECT * FROM USERS
SELECT * FROM USER_PHONE
DELETE USERS WHERE USR_ID=2
SELECT * FROM USER_PHONE
DROP TABLE USER_PHONE
DROP TABLE USERS

No comments:

Post a Comment