?
Solved

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

Posted on 2007-04-10
7
Medium Priority
?
349 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
[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
  • 4
  • 3
7 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 18885977
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
ID: 18887399
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
ID: 18887412
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:ckangas7
ID: 18891281
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 1000 total points
ID: 18893090
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
ID: 18893160
Yep that did it.  Great, that's one more step completed.  Thanks much.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 18893183
You are welcome.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

764 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