SQL Tips by Namwar Rizvi

August 26, 2008

Deleting ununsed primary keys

Filed under: Query,tips,TSQL — namwar @ 10:35 PM
Tags: , ,

Sometimes we need to make sure that all keys in the primary key table has atleast one corresponding row in child table or in other words every key has been used atleast once as a foreign key. This is a typical scenario we face during data import where data integrity can not be guarnteed 100% due to various reason. Following script demonstrate a simple technique to delete all those rows in the master table which are not used in child table.

Use tempdb;

/* Create sample tables and insert sample data */

Create table tblMaster

(

masterId int,

masterName varchar(50)

)

Go

Create table tblChild

(

childId int NOT NULL IDENTITY (1, 1),

masterId int,

childName varchar(50)

)

Go

Insert into tblMaster (masterId,masterName) values (1,‘Master 1’);

Insert into tblMaster (masterId,masterName) values (2,‘Master 2’);

Insert into tblMaster (masterId,masterName) values (3,‘Master 3’);

Insert into tblMaster (masterId,masterName) values (4,‘Master 4’);

Go

Insert into tblChild (masterId,childName) values (1,‘Child 1’);

Insert into tblChild (masterId,childName) values (1,‘Child 2’);

Insert into tblChild (masterId,childName) values (1,‘Child 3’);

Insert into tblChild (masterId,childName) values (2,‘Child 4’);

Go

/* Master table before deletion */

Select * from tblMaster

Go

/* Delete unused rows */

Delete tblMaster

where

not exists

(

Select masterId

from tblChild

where

tblChild.masterId=tblMaster.masterId

)

Go

/* Master table after deletion */

Select * from tblMaster

Go

/* Clean up */

drop table tblMaster;

Go

drop table tblChild;

Go

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: