Solved

How to Truncate table with Foreign Key Constraint

Posted on 2013-12-07
22
31,105 Views
Last Modified: 2013-12-09
Hello there,


I have two table supplierorder and supplierorderdetails. When I try to truncate the supplierorder I get constraint error on supplierorder .  I also tried to truncate the child table first but still I get this error. I also tried running this query but no luck. by the way where in the management studio I can see the constraint name

ALTER TABLE supplierorderdetails
NOCHECK CONSTRAINT [FK49C8CB00E0E53A9F]
GO

TRUNCATE TABLE supplierorder
GO

ALTER TABLE supplierorderdetails
WITH CHECK CHECK CONSTRAINT [FK49C8CB00E0E53A9F]
GO

Open in new window


the error I get is

11:27:23  [ALTER - 0 row(s), 0.010 secs]  Command processed. No rows were affected
 11:27:23  [TRUNCATE - 0 row(s), 0.000 secs]  [Error Code: 4712, SQL State: 23000]  Cannot truncate table 'supplierorder' because it is being referenced by a FOREIGN KEY constraint.
 11:27:23  [ALTER - 0 row(s), 0.000 secs]  Command processed. No rows were affected
0
Comment
Question by:zolf
  • 11
  • 5
  • 5
  • +1
22 Comments
 
LVL 9

Assisted Solution

by:QuinnDex
QuinnDex earned 50 total points
ID: 39702790
use this to find the constraints on the table, both tables may have constraints so check both, change table name accordingly

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('supplierorder')

Open in new window


when you have determined what constraints are on the tables you can remove them with something like this


SELECT 
    'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    '.' + OBJECT_NAME(parent_object_id) + 
    ' DROP CONSTRAINT ' + name
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('supplierorder')

Open in new window

0
 

Author Comment

by:zolf
ID: 39702810
thanks for your comments.

what does this query do. does it again restore the constraint.
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39702825
no it only removes it, if you need to restore it this is a better way

to temporarily disable all restraints

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' NOCHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;

Open in new window


To re-enable:

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;

Open in new window

0
 

Author Comment

by:zolf
ID: 39702838
thanks once again.

this parent_object_id is the id for the child or parent table.
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39702844
that will be the id in the sys table that the constraint relates to, on those queries you dont need to edit they will run as they are
0
 

Author Comment

by:zolf
ID: 39702856
my apologies.. how do you run those query.
0
 

Author Comment

by:zolf
ID: 39702897
I get this error when I try to run

Must declare the scalar variable "@sql".
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39702927
it is declared in the query, run it in SQL server management studio under master
0
 

Author Comment

by:zolf
ID: 39702948
I still get that constraint error
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39703087
past in the query you are trying to run...

open SQL server management studio

click new query

past into the page the complete code below

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' NOCHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;
                                            

Open in new window


constraints are now disabled

change current database to the database where you need to truncate tables
truncate the tables that you need to truncate
change current database back to Master

then past in the complete code below

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS 
(
  SELECT DISTINCT obj = 
      QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
    + QUOTENAME(OBJECT_NAME(parent_object_id)) 
  FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;
                                            

Open in new window



constraints will now be re enabled
0
 

Author Comment

by:zolf
ID: 39703961
this is the query I am trying to run and I am follwoign your instructions but I still get that constraint error

truncate table supplierOrderdetails   // child

truncate table supplierorder        //parent

Open in new window

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 350 total points
ID: 39703972
Hey guys, from BOL on TRUNCATE
Restrictions
You cannot use TRUNCATE TABLE on tables that:
      •Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
      •Participate in an indexed view.
      •Are published by using transactional replication or merge replication.
For tables with one or more of these characteristics, use the DELETE statement instead.
And trying to Disable them is not enough, just the existence of them prevents the truncate from working.
Also, please be aware that doing a mass operation like you are suggesting removes all Referential Integrity on your database.  If not done inside a transaction, it opens up all your tables to bad data.
The only real way is to do it is to script the drop of the FK to the table being Truncated and then script adding it back.  Sorry there is not a better answer.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 39704150
Here is an example of how you would do this, I have a table State with a FK to Country and to truncate both of them I have to do it in a script like this:
BEGIN TRY
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	-- SET NOCOUNT ON;

	BEGIN TRANSACTION;

	
ALTER TABLE [dbo].[State] DROP CONSTRAINT [FK_State_Country]

TRUNCATE TABLE dbo.State;
TRUNCATE TABLE dbo.Country;

ALTER TABLE [dbo].[State]  WITH CHECK ADD  CONSTRAINT [FK_State_Country] FOREIGN KEY([CountryId])
REFERENCES [dbo].[Country] ([Id])

ALTER TABLE [dbo].[State] CHECK CONSTRAINT [FK_State_Country]


	COMMIT TRANSACTION;

END TRY
BEGIN CATCH

	IF @@TRANCOUNT > 0
		ROLLBACK;

	DECLARE @ProcName NVARCHAR(100);
	SELECT @ProcName = COALESCE(OBJECT_NAME(@@procid),ERROR_PROCEDURE());
	EXEC G2.Tools.LogError @ProcName;

END CATCH

Open in new window

0
 
LVL 5

Assisted Solution

by:rk_india1
rk_india1 earned 100 total points
ID: 39704348
I am follow the below guideline

1.Drop the constraints from Child table
2.Trunc the table
3.Recreate the constraints.

(All in a transaction, of course.)



--Drop the foreign key
ALTER TABLE dbo.supplierorderdetails
DROP CONSTRAINT FK49C8CB00E0E53A9F

GO
 
truncate table dbo.supplierorder
truncate table dbo.supplierorderdetails
GO
 
--Add Foreign key back
 
ALTER TABLE dbo.supplierorderdetails ADD CONSTRAINT
 
FK_CategoryID FOREIGN KEY
 
( CatID )
REFERENCES dbo.supplierorder
( FK49C8CB00E0E53A9F )
 
GO
0
 

Author Comment

by:zolf
ID: 39705528
by: rk_india1

what is that ( CatID )

I get this error when I run your query

Foreign key 'FK_CategoryID' references invalid column 'CatID' in referencing table 'supplierorderdetails'. 2) [Error Code: 1750, SQL State: S1000]  Could not create constraint. See previous errors.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 39705555
what is that ( CatID )
I am sure that is a guess by rk_india1 for your column name.  You have not given us all the details to write your exact query for you.  His example and the one I gave before it are trying to teach you how to script this type of action as you have to remove all FK constraints pointing to the table you want to Truncate and then put them back afterwards.
0
 

Author Comment

by:zolf
ID: 39705598
by: CGLuttrell
thanks for your comments

CREATE TABLE
    supplierOrder
    (
        ID BIGINT NOT NULL IDENTITY,
        sectionID BIGINT,
        supplierID INT,
        supplierOrderNo INT,
        supplierOrderCreated DATETIME,
        created DATETIME,
        lastUpdated DATETIME,
        createdBy BIGINT,
        updatedBy BIGINT,
        PRIMARY KEY (ID),
        CONSTRAINT FK7B62DCE259E39C5 FOREIGN KEY (sectionID) REFERENCES Section (id),
        CONSTRAINT FK7B62DCE2CE67ED93 FOREIGN KEY (supplierID) REFERENCES Supplier (id)
    );

Open in new window




CREATE TABLE
    supplierOrderDetails
    (
        id BIGINT NOT NULL IDENTITY,
        supplierOrderID BIGINT,
        productID BIGINT,
        orderPoint INT,
        stockCount INT,
        lastMonthSales INT,
        avgMonthlySales INT,
        avgMonthlySales_stock INT,
        orderQty INT,
        purchasePrice INT,
        totalAmount INT,
        created DATETIME,
        lastUpdated DATETIME,
        createdBy BIGINT,
        updatedBy BIGINT,
        PRIMARY KEY (id),
        CONSTRAINT FK49C8CB00677F7B59 FOREIGN KEY (productID) REFERENCES product (id),
        CONSTRAINT FK49C8CB00E0E53A9F FOREIGN KEY (supplierOrderID) REFERENCES supplierOrder (ID)
    );

Open in new window

0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 350 total points
ID: 39705609
ok, try this
BEGIN TRY
	BEGIN TRANSACTION;
	
	ALTER TABLE supplierOrderDetails DROP CONSTRAINT FK49C8CB00E0E53A9F 

	TRUNCATE TABLE supplierOrderDetails ;
	TRUNCATE TABLE supplierOrder ;

	ALTER TABLE supplierOrderDetails WITH CHECK ADD  CONSTRAINT FK49C8CB00E0E53A9F  
	FOREIGN KEY (supplierOrderID)
	REFERENCES supplierOrder (ID)

	ALTER TABLE supplierOrderDetails CHECK CONSTRAINT FK49C8CB00E0E53A9F 

	COMMIT TRANSACTION;

END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
		ROLLBACK;
END CATCH

Open in new window

0
 

Author Comment

by:zolf
ID: 39705623
I get this error when I run the query.can you please have a look

13:00:11  [BEGIN - 0 row(s), 0.000 secs]  [Error Code: 102, SQL State: 42000]  Incorrect syntax near 'TRANSACTION'.
 13:00:11  [ALTER - 0 row(s), 0.005 secs]  Command processed. No rows were affected
 13:00:11  [TRUNCATE - 0 row(s), 0.002 secs]  Command processed. No rows were affected
 13:00:11  [ALTER - 0 row(s), 0.000 secs]  [Error Code: 3902, SQL State: 25000]  The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
 13:00:11  [END - 0 row(s), 0.000 secs]  [Error Code: 102, SQL State: 42000]  Incorrect syntax near 'TRY'.
 13:00:11  [END - 0 row(s), 0.000 secs]  [Error Code: 102, SQL State: 42000]  Incorrect syntax near 'CATCH'.
... 6 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.007/0.000 sec  [0 successful, 2 warnings, 4 errors]
0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 350 total points
ID: 39706277
Where are you running this from? I am not familiar with the style of those error messages.  I tested and ran my code from Management Studio and it works fine.  Can you tell us where you are running it from (what tool) and post your actual code, maybe you have a syntax error in your translation or something?
0
 

Author Comment

by:zolf
ID: 39706314
ic.i am using dbvis.let me try studio and I will let you know
0
 

Author Closing Comment

by:zolf
ID: 39707805
cheers
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

746 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

10 Experts available now in Live!

Get 1:1 Help Now