How to Truncate table with Foreign Key Constraint

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
zolfAsked:
Who is Participating?
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
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
 
QuinnDexConnect With a Mentor Commented:
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
 
zolfAuthor Commented:
thanks for your comments.

what does this query do. does it again restore the constraint.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
QuinnDexCommented:
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
 
zolfAuthor Commented:
thanks once again.

this parent_object_id is the id for the child or parent table.
0
 
QuinnDexCommented:
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
 
zolfAuthor Commented:
my apologies.. how do you run those query.
0
 
zolfAuthor Commented:
I get this error when I try to run

Must declare the scalar variable "@sql".
0
 
QuinnDexCommented:
it is declared in the query, run it in SQL server management studio under master
0
 
zolfAuthor Commented:
I still get that constraint error
0
 
QuinnDexCommented:
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
 
zolfAuthor Commented:
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
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
rk_india1Connect With a Mentor Commented:
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
 
zolfAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
zolfAuthor Commented:
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
 
zolfAuthor Commented:
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
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
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
 
zolfAuthor Commented:
ic.i am using dbvis.let me try studio and I will let you know
0
 
zolfAuthor Commented:
cheers
0
All Courses

From novice to tech pro — start learning today.