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
