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.
Solved
SQL Server Item Purge w/ Foreign Key Constraints
Posted on 2011-02-23
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'
Much Regards,
Traci M.
delete from dimitem where
itemwk not in (select distinct(itemwk) from factcustsales)and datepart(year, effbegindate) <= '2008'
[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
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
--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
ALTER TABLE [dbo].[DimItem] DROP CONSTRAINT [DF_DimItem_BrandFamilyID]
ALTER TABLE [dbo].[DimItem] DROP CONSTRAINT [DF_DimItem_BrandFamilyDes
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_BrandFamilyDes
ALTER TABLE [dbo].[DimItem] ADD CONSTRAINT [DF_DimItem_BrandFamilyID]
ALTER TABLE [dbo].[DimItem] DROP CONSTRAINT [DF_DimItem_BrandFamilyDes
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_BrandFamilyDes
ALTER TABLE [dbo].[DimItem] ADD CONSTRAINT [DF_DimItem_BrandFamilyID]
Featured Post
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…
Suggested Courses
Course of the Month8 days, 18 hours left to enroll
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.