Problem

While designing database structure, we often use FOREIGN KEYS as a technique to display the parent-child relationship among tables. However, if we want to delete data of parent table, we need to delete data of child tables before. So we will maybe face to a problem if there are many parent-child relationships.

For example, you have a parent table A and five child tables (B,C,D,E,F). And you want to delete one record of A, which also is existed in 5 child-tables, you need to delete data of all of child-tables before deleting A. The question is here THERE IS ANY FASTER WAY TO DELETE ONE RECORD OF A?

Solution

Microsoft SQL Server provides us the options DELETE CASCADE and UPDATE CASCADE while creating FOREIGN KEYS to help us delete data quicker. For demonstration purpose, I have tables PUPILS, OBJECTS and REGISTRATION to mange information of each pupil and their registration information.

CREATE TABLE [dbo].[Pupils](
[PupilID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
[Name] [nvarchar](255) NOT NULL)

CREATE TABLE [dbo].[Objects](
[ObjectID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
[Name] [nvarchar](255) NOT NULL)

CREATE TABLE [dbo].[Resgitrations](
[PupilID] [int] NOT NULL,
[ObjectID] [int] NOT NULL,
[Result] [nvarchar](50) NULL
 CONSTRAINT [PK_Resgitrations] PRIMARY KEY CLUSTERED 
( [PupilID] ASC,
[ObjectID] ASC)
);
GO
--> INSERT DATA
SET IDENTITY_INSERT [Pupils] ON;
INSERT INTO Pupils(PupilID,Name)
VALUES(1,'Nguyen Van A'),(2,'Nguyen Van B'),(3,'Nguyen Van C')
SET IDENTITY_INSERT [Pupils] OFF;

SET IDENTITY_INSERT [Objects] ON;
INSERT INTO [Objects]([ObjectID],Name)
VALUES(1,'Toán'),(2,'Lý'),(3,'Hóa')
SET IDENTITY_INSERT [Objects] OFF;
GO

CASE 1: we create the foreign keys without UPDATE CASCADE and DELETE CASCADE

ALTER TABLE [Resgitrations] ADD CONSTRAINT [FK_Pupils_Resgitrations] FOREIGN KEY(PupilID) REFERENCES  [dbo].[Pupils](PupilID)
ALTER TABLE [Resgitrations] ADD CONSTRAINT [FK_Objects_Resgitrations] FOREIGN KEY(ObjectID) REFERENCES  [dbo].[Objects](ObjectID)

GO
-->INSERT DATA TO [Resgitrations]
INSERT INTO Resgitrations
VALUES(1,1,'Success'),(1,2,'Fail'),(1,3,'Pending')
,(2,1,'Success'),(2,2,'Fail'),(3,1,'Pending'),(3,2,'Pending')

Now, we delete the pupil with ID =3 and an error message will be raised

DELETE FROM [Pupils] WHERE PupilID = 3

Msg 547, Level 16, State 0, Line 42
The DELETE statement conflicted with the REFERENCE constraint “FK_Pupils_Resgitrations”. The conflict occurred in database …..column ‘PupilID’.
The statement has been terminated.

CASE 2: we create the foreign keys with UPDATE CASCADE and DELETE CASCADE

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Objects_Resgitrations]') AND parent_object_id = OBJECT_ID(N'[dbo].[Resgitrations]'))
ALTER TABLE [dbo].[Resgitrations] DROP CONSTRAINT [FK_Objects_Resgitrations]
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Objects_Resgitrations]') AND parent_object_id = OBJECT_ID(N'[dbo].[Resgitrations]'))
ALTER TABLE [dbo].[Resgitrations]  WITH CHECK ADD  CONSTRAINT [FK_Objects_Resgitrations] FOREIGN KEY([ObjectID])
REFERENCES [dbo].[Objects] ([ObjectID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Objects_Resgitrations]') AND parent_object_id = OBJECT_ID(N'[dbo].[Resgitrations]'))
ALTER TABLE [dbo].[Resgitrations] CHECK CONSTRAINT [FK_Objects_Resgitrations]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Pupils_Resgitrations]') AND parent_object_id = OBJECT_ID(N'[dbo].[Resgitrations]'))
ALTER TABLE [dbo].[Resgitrations] DROP CONSTRAINT [FK_Pupils_Resgitrations]
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Pupils_Resgitrations]') AND parent_object_id = OBJECT_ID(N'[dbo].[Resgitrations]'))
ALTER TABLE [dbo].[Resgitrations]  WITH CHECK ADD  CONSTRAINT [FK_Pupils_Resgitrations] FOREIGN KEY([PupilID])
REFERENCES [dbo].[Pupils] ([PupilID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Pupils_Resgitrations]') AND parent_object_id = OBJECT_ID(N'[dbo].[Resgitrations]'))
ALTER TABLE [dbo].[Resgitrations] CHECK CONSTRAINT [FK_Pupils_Resgitrations]
GO

Continue deleting the pupil with ID =3, the statement was run successfully

DELETE FROM [Pupils] WHERE PupilID = 3
GO
SELECT * FROM Pupils WHERE PupilID = 3
SELECT * FROM Resgitrations WHERE PupilID = 3
-- No record

CONCLUSION

These options help us delete data quicker instead of deleting data of each child table and guarantee the ACID of our database. However, depending on your requirement, you should use them appropriately.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s