Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Course Of The Month
10 days, 3 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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
Hi all,
It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month10 days, 3 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.