Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Item Purge w/ Foreign Key Constraints

Posted on 2011-02-23
3
Medium Priority
?
394 Views
Last Modified: 2012-05-11
I would like to purge some items out of our sql table that has foreign key constraints associated with it. I have saved these specific items to a historical table. What is the best practice in deleting these items from this type of table with regard to the foreign keys? Do I have to drop and recreated the keys? If so, what is the proper way in doing so, so that I do not disturb the data I still want retained in the table.

Much Regards,

Traci M.

delete from dimitem  where
itemwk not in (select distinct(itemwk) from factcustsales)and datepart(year, effbegindate) <= '2008'
0
Comment
Question by:SasDev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
rushShah earned 2000 total points
ID: 34962955
You have to drop that foreign key constraint then delete data and then recreate constraint.

--drop foreign key

alter table tablename drop constraint ConstraintName

-- Delete data
delete from dimitem  where
itemwk not in (select distinct(itemwk) from factcustsales)and datepart(year, effbegindate) <= '2008'

--Recreate Constraint
ALTER TABLE [dbo].[TableName]
  WITH CHECK ADD CONSTRAINT [FK_TableName_TableName2] FOREIGN KEY([FK_Name])
    REFERENCES [dbo].[TableName2] ([ID])
go
0
 
LVL 1

Author Comment

by:SasDev
ID: 34963044
ALTER TABLE [dbo].[DimItem] DROP CONSTRAINT [DF_DimItem_BrandFamilyID]
ALTER TABLE [dbo].[DimItem] DROP CONSTRAINT [DF_DimItem_BrandFamilyDesc]

delete from dimitem  where
itemwk not in (select distinct(itemwk) from factcustsales)and datepart(year, effbegindate) <= '2008'

ALTER TABLE [dbo].[DimItem] ADD  CONSTRAINT [DF_DimItem_BrandFamilyDesc]  DEFAULT (' ') FOR [BrandFamilyDesc]
ALTER TABLE [dbo].[DimItem] ADD  CONSTRAINT [DF_DimItem_BrandFamilyID]  DEFAULT (' ') FOR [BrandFamilyID]
0
 
LVL 1

Author Closing Comment

by:SasDev
ID: 34963054
Thank you!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question