Deleting Primary Key Data without Deleting Foreign Key Data

Some times we face this situation where we need to delete only primary key column data and it should not effect the foreign key column data.

Here i have taken some example to elaborate it.

Create table PrimaryKeyTable
(
id int identity (1,1) primary key,
[name] varchar(100)
)

create table ForeignKeyTable
(
id int foreign key ([id]) references PrimaryKeyTable ([id]),
[age] int
)
insert into PrimaryKeyTable values (‘ram’)
insert into PrimaryKeyTable values (‘sam’)
insert into PrimaryKeyTable values (‘hari’)
insert into PrimaryKeyTable values (‘raju’)
insert into PrimaryKeyTable values (‘manju’)

Select * From PrimaryKeyTable

Out put:

id name
———–
1 ram
2 sam
3 hari
5 manju

insert into ForeignKeyTable values (1,25)
insert into ForeignKeyTable values (2,24)
insert into ForeignKeyTable values (3,29)
insert into ForeignKeyTable values (4,20)
insert into ForeignKeyTable values (5,27)

Select * From ForeignKeyTable

Out put

id age
———– ———–
1 25
2 24
3 29
4 20
5 27

ALTER TABLE ForeignKeyTable NOCHECK CONSTRAINT FK__PrimaryKeyTable__id__5E94F66B

Delete FROM PrimaryKeyTable WHERE id = 4

Select * From PrimaryKeyTable

id name
———–
1 ram
2 sam
3 hari
5 manju

Now you can enable the foreign key constraint

ALTER TABLE ForeignKeyTable CHECK CONSTRAINT FK__PrimaryKeyTable__id__5E94F66B

You might have a question that how should i enable and disable all foreign constraint in the database

The below script generate disable

SELECT  'IF EXISTS (SELECT * FROM sys.foreign_keys 
   WHERE object_id = OBJECT_ID(N''[dbo].' + FK +''') 
   AND parent_object_id = OBJECT_ID(N''[dbo].' + PT + ''')) 
   ALTER TABLE ' + PT + ' NOCHECK CONSTRAINT ' + FK + ';'
FROM 
(SELECT 
    OBJECT_NAME(constraint_object_id) as FK,
    OBJECT_NAME(parent_object_id) as PT
    FROM [sys].[foreign_key_columns] ) T
ORDER BY FK

the below script generate enable

SELECT  'ALTER TABLE ' + PT + ' WITH CHECK CHECK CONSTRAINT ' + FK + ';'
FROM 
(SELECT 
    OBJECT_NAME(constraint_object_id) as FK,
    OBJECT_NAME(parent_object_id) as PT
    FROM [sys].[foreign_key_columns] ) T
ORDER BY FK

-- Disable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Enable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

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