Solved

Need script to Remove Foreign Keys, Truncate Table, and Recreate Foreign Keys with No check

Posted on 2007-04-10
7
332 Views
Last Modified: 2008-01-09
Am trying to truncate a table "tblDoucumentInfo" but get an error becuase the table contains Foreign Key Constraints.  From what I have read the best way to do this, as opposed to delete and recreate the table is to remove the contraints, truncate the table, and recreate the contraints with the no check option.  Doing an sp_help on the table shows the following foreign keys:

Reports.dbo.tblFees: FK__tblFees__iRecord__69C6B1F5
Reports.dbo.tblLand: FK__tblLand__iRecord__6ABAD62E
Reports.dbo.tblParty: FK__tblParty__iRecor__6E8B6712
Reports.dbo.tblRelated: FK__tblRelate__iReco__6F7F8B4B
Reports.dbo.tblUserField: FK__tblUserFi__iReco__7073AF84

All the tables will be empty, so referential integrity should be ok.  Data will later be intserted into tblDocumentInfo, and tblParty for the purpose of creating reports.  It is those two tables that I need to truncate everytime I want to run reports.  tblParty truncates fine.

So what would be the correct script to remove the FK contstraints, truncate the tblDocumentInfo table, and then recreate the FKs?
0
Comment
Question by:ckangas7
  • 4
  • 3
7 Comments
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi,

To remove the foreign keys

IF EXISTS
      (
      SELECT * from sysobjects
      where name='FK__tblFees__iRecord__69C6B1F5' and xtype='F' and object_name(parent_obj) = 'tblFees'
      )
      Alter table tblFees
      drop constraint FK__tblFees__iRecord__69C6B1F5

-- I would suggest that when you create them again that you name them to remove the 8 character number
-- My Guess on the add constraint is:
ALTER TABLE tblFees WITH CHECK ADD CONSTRAINT 'FK__tblFees__iRecord'
      FOREIGN KEY (iRecord) REFERENCES tblDoucumentInfo(iRecord)

I'd suggest that you script out the foreign keys using Query Analyser, to make
sure that you get the re-create part right.

In Query Analyser
In Object Explorer, drill down to the table, and then to the Constraints.
Right click on the constraint in question and choose script to new window.

Remember, these foreign keys are on the five tables that reference tblDocumentInfo!
ie, tblFees, tblLand, tblParty, tblRelate, tblUserFi

HTH
  David

0
 

Author Comment

by:ckangas7
Comment Utility
Hey David,

My problem now is that when I recreate the constraints the 8 character number is automatically created and it is different each time I delete and recreate them.  When I drill down to the constraints in Query Analzyer the script reads something like the following for each key:
ALTER TABLE [dbo].[tblFees] ADD  FOREIGN KEY
      (
            [iRecordId]
      ) REFERENCES [tblDocumentInfo] (
            [iRecordId]
      )
GO

So is there a way I can get around the 8 charater number thing?  Perhaps use a wild card in the drop statement?
 ie Alter table tblFees  drop constraint FK__tblFees__iRecord__%
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi,

The problem is that you need to name the constraint - your code doesn't.

Try

ALTER TABLE [dbo].[tblFees]
--
WITH CHECK ADD CONSTRAINT 'FK__tblFees__iRecord'
--
FOREIGN KEY
      (
            [iRecordId]
      ) REFERENCES [tblDocumentInfo] (
            [iRecordId]
      )
GO

HTH
  David
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:ckangas7
Comment Utility
Ok, we are halfway there.  I got all the drop statements to work.  I created the below to add the constraints back in, but am gettting a  "Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'FK__tblAddress__iRecord'" error for each of the keys.  Script reads as follows:
ALTER TABLE [dbo].[tblAddress]
WITH CHECK ADD CONSTRAINT 'FK__tblAddress__iRecord'
FOREIGN KEY
      (
            [iRecordId]
      ) REFERENCES [tblDocumentInfo] (
            [iRecordId]
      )
GO
ALTER TABLE [dbo].[tblDocumentHistory]
WITH CHECK ADD CONSTRAINT 'FK__DocumentHistory__iRecord'
FOREIGN KEY
      (
            [iRecordId]
      ) REFERENCES [tblDocumentInfo] (
            [iRecordId]
      )
GO
ALTER TABLE [dbo].[tblFees]
WITH CHECK ADD CONSTRAINT 'FK__tblFees__iRecord'
FOREIGN KEY
      (
            [iRecordId]
      ) REFERENCES [tblDocumentInfo] (
            [iRecordId]
      )
GO
ALTER TABLE [dbo].[tblLand]
WITH CHECK ADD CONSTRAINT 'FK__tblLand__iRecord'
FOREIGN KEY
      (
            [iRecordId]
      ) REFERENCES [tblDocumentInfo] (
            [iRecordId]
      )
GO
ALTER TABLE [dbo].[tblParty]
WITH CHECK ADD CONSTRAINT 'FK__tblParty__iRecord'
FOREIGN KEY
      (
            [iRecordId]
      ) REFERENCES [tblDocumentInfo] (
            [iRecordId]
      )
GO
ALTER TABLE [dbo].[tblRelated]
WITH CHECK ADD CONSTRAINT 'FK__tblRelated__iRecord'
FOREIGN KEY
      (
            [iRecordId]
      ) REFERENCES [tblDocumentInfo] (
            [iRecordId]
      )
GO
ALTER TABLE [dbo].[tblUserField]
WITH CHECK ADD CONSTRAINT 'FK__tblUserField__iRecord'
FOREIGN KEY
      (
            [iRecordId]
      ) REFERENCES [tblDocumentInfo] (
            [iRecordId]
      )
GO
0
 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
Comment Utility
Hi,

My mistake - problem is the quotes around the contstraint name.

You could try this in pubs:

use pubs
go

alter table dbo.titleauthor drop constraint FK__titleauth__au_id__0519C6AF
go

ALTER TABLE  dbo.titleauthor
--
WITH CHECK
ADD
CONSTRAINT
FK__titleauth__au_id__0519C6AF
--
FOREIGN KEY
      (
            [au_id]
      ) REFERENCES [authors] (
            [au_id]
      )
GO

HTH
  David
0
 

Author Comment

by:ckangas7
Comment Utility
Yep that did it.  Great, that's one more step completed.  Thanks much.
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
You are welcome.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 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

12 Experts available now in Live!

Get 1:1 Help Now