Solved

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

Posted on 2007-04-10
7
346 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

719 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