Solved

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

Posted on 2007-04-10
7
336 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 24
RAISERROR WITH NOWAIT 2 16
Substring works but need to tweak it 14 14
Stored Procedure needs owner to execute 5 15
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

856 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