Solved

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

Posted on 2007-04-10
7
335 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
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 250 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

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.  

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

810 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