How to Truncate table with Foreign Key Constraint

thanks for your comments.

what does this query do. does it again restore the constraint.

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;

Select allOpen 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;

Select allOpen in new window

thanks once again.

this parent_object_id is the id for the child or parent table.

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

my apologies.. how do you run those query.

I get this error when I try to run

Must declare the scalar variable "@sql".

it is declared in the query, run it in SQL server management studio under master

I still get that constraint error

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;
                                            

Select allOpen 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;
                                            

Select allOpen in new window

constraints will now be re enabled

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

Select allOpen in new window

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

Select allOpen in new window

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.

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.

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)
    );

Select allOpen 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)
    );

Select allOpen in new window

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]

ic.i am using dbvis.let me try studio and I will let you know

cheers