Solved

SQL Server Item Purge w/ Foreign Key Constraints

Posted on 2011-02-23
3
386 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
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
rushShah earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now