Disabling foreign key constraint, still can't truncate table? (SQL Server 2005)

disable foreign key constraint sql server
disable all foreign key constraint sql server
disable foreign key constraint oracle
disable foreign key constraint mariadb
disable foreign key constraint postgres
cannot add foreign key constraint disable
set key constraints mysql
disable constraint check

I have a table called PX_Child that has a foreign key on PX_Parent. I'd like to temporarily disable this FK constraint so that I can truncate PX_Parent. I'm not sure how this goes however.

I've tried these commands

ALTER TABLE PX_Child NOCHECK CONSTRAINT ALL

ALTER TABLE PX_Parent NOCHECK CONSTRAINT ALL

(truncate commands)

ALTER TABLE PX_Child CHECK CONSTRAINT ALL

ALTER TABLE PX_Parent CHECK CONSTRAINT ALL

But the truncate still tells me it can't truncate PX_Parent because of a foreign key constraint. I've looked all around the net and can't seem to find what I'm doing wrong, sorry for the basic nature of this question.

You can't truncate the table if there is any foreign key referencing it, including disabled constraints. You either need to drop the foreign key constraints or use the DELETE command.

How can foreign key constraints be temporarily disabled using T-SQL?, You can disable a foreign key constraint during INSERT and UPDATE transactions in SQL Server 2019 (15.x) by using SQL Server Management� You can use the code below to disable all CHECK and foreign key constraints for a specific table in SQL Server. Just replace TableName with the name of the applicable table. ALTER TABLE TableName NOCHECK CONSTRAINT ALL Below is an example where I do this and then check the result.

There is an easier-ish way. I was faced with the same problem and found this solution: https://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/

If you just run this query in your DB, it will generate the T-SQL you need to include before/after your sproc, in order to delete and then restore any foreign key constraints.

Don't worry about trying to understand this query itself.

CREATE TABLE #x -- feel free to use a permanent table
(
  drop_script NVARCHAR(MAX),
  create_script NVARCHAR(MAX)
);

DECLARE @drop   NVARCHAR(MAX) = N'',
        @create NVARCHAR(MAX) = N'';

-- drop is easy, just build a simple concatenated list from sys.foreign_keys:
SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id];

INSERT #x(drop_script) SELECT @drop;

-- create is a little more complex. We need to generate the list of 
-- columns on both sides of the constraint, even though in most cases
-- there is only one column.
SELECT @create += N'
ALTER TABLE ' 
   + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
   + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) 
   + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
   -- get all the columns in the constraint table
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
  + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
  + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
   -- get all the referenced columns
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs 
  ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;

UPDATE #x SET create_script = @create;

PRINT @drop;
PRINT @create;

/*
EXEC sp_executesql @drop
-- clear out data etc. here
EXEC sp_executesql @create;
*/

Generates a bunch of:

ALTER TABLE [dbo].[Whatever] DROP CONSTRAINT....
--
ALTER TABLE [dbo].[Whatever] ADD CONSTRAINT....

Disable Foreign Key Constraints in INSERT & UPDATE Statements , For example, you can load data to the parent and child tables in any order with the foreign key constraint check disabled. If you don't disable foreign key checks, � You can't truncate the table if there is any foreign key referencing it, including disabled constraints. You either need to drop the foreign key constraints or use the DELETE command. share | improve this answer answered Oct 2 '10 at 0:23

SQL server will not let you truncate the table while the constraint exists, even if it's disabled. Drop the constraint and re-create it after truncating the table. Or just drop and re-create the tables, whichever is easier to do in your application.

How to Disable Foreign Key Constraint Checks in MySQL, Learn how to disable a foreign key in SQL Server with syntax and examples. Once you have of this website. You can learn more about how we use advertisements here. ALTER TABLE table_name NOCHECK CONSTRAINT fk_name;� I have a table called PX_Child that has a foreign key on PX_Parent. I'd like to temporarily disable this FK constraint so that I can truncate PX_Parent. I'm not sure how this goes however.

There is no such option to truncate table while foreign key constraint but we can use some trick like

 ALTER TABLE [dbo].[table2] DROP CONSTRAINT [FK_table2_table1]
    GO
    truncate table [table1]
GO
    ALTER TABLE [dbo].[table2]  WITH CHECK ADD  CONSTRAINT [FK_table2_table1] FOREIGN KEY([FKId])
    REFERENCES [dbo].[table1] ([ID])
    GO

    ALTER TABLE [dbo].[table2] CHECK CONSTRAINT [FK_table2_table1]
    GO

SQL Server: Disable a foreign key, This Oracle tutorial explains how to disable a foreign key in Oracle with syntax and examples. Once you You can learn more about how we use advertisements here. ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;� To disable a foreign key constraint for INSERT and UPDATE statements In Object Explorer, expand the table with the constraint and then expand the Keys folder. Right-click the constraint and select Modify. In the grid under Table Designer, click Enforce Foreign Key Constraint and select No from the drop-down menu.

Oracle / PLSQL: Disable a foreign key, Then when you enable foreign key constraints check, MySQL does not set ( 0.00 sec) -- Row violating foreign key constraint still exists SELECT * FROM cities ;� You can't truncate the table if there is any foreign key referencing it, including disabled constraints. You either need to drop the foreign key constraints or use the DELETE command.

MySQL - Check or Not Foreign Key Constraints, Here is a quick script I have written in my early career which I still use it when I need to do something similar. -- Disable all the constraint in� NOCheck disable the constraints so they won't be enforced. This would allow you to delete the data without violating the constraint. Dropping the table would make the constraint definition invalid. You can't have a constraint that references a table that doesn't exist.

SQL SERVER, Having untrusted or disabled FOREIGN KEY or CHECK constraints in your databases will degrade data consistency and integrity and can� There is one more thing, when you have disabled the constraint, you can delete the data from the table but if you attempt to truncate the table, it will still give you an error. If you need to truncate the table you will have to actually drop all the constraints. Do you use similar script in your environment?

Comments
  • Looks like Kalen Delaney was inadvertently responsible for starting this idea off. Here she clarifies "you have to drop the referencing constraint in order to truncate the table."
  • See my (5 years later) answer below for how to quickly generate the DROP CONSTRAINT and ADD CONSTRAINT SQL
  • The DELETE statement conflicted with the REFERENCE constraint. (Delete is not working either)
  • bad advice: "Don't worry about trying to understand this query itself". Never run anything gotten off the net without understanding it
  • True, but it wasn't a production version of the DB
  • There is a difference between understanding how a query works and making sure it won't do any harm. The latter is almost always easier.
  • As written this doesn't properly recreate constraints with 'ON DELETE CASCADE` rules. It's a simple fix, change the line FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');' to FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ') ON DELETE ' + REPLACE(fk.delete_referential_action_desc, '_', ' ') + ';'
  • Note that the PRINT @drop and PRINT @create statements will truncate in SQL Management Studio. If you want these full values you will need to SELECT * FROM #x and then copy values from the two resulting columns. Even then I ended up having to DECLARE @tempCreate NVARCHAR(MAX); SELECT @tempCreate = create_script from #x; EXEC sp_executesql @tempCreate; for my create statement.
  • What do you mean it's not a transactional command? You can roll it back just fine. CREATE TABLE Blah(a int); INSERT Blah VALUES(1); SELECT * FROM Blah; BEGIN TRAN; TRUNCATE TABLE Blah; SELECT * FROM Blah; ROLLBACK TRAN SELECT * FROM Blah; DROP TABLE Blah. Truncate works by deallocating entire pages rather than removing rows, but it's still transactional.
  • @Emtucifor: Oops, seems like I misinterpreted the documentation you you're right! I removed that piece of misinformation.
  • @Emtucifor, @pgroke, in a way you are both correct as the standard allows for TRUNCATE to be non-transactional, but implementations are allowed to make it transaction. Hence TRUNCATE as defined doesn't promise a rollback can be done, but SqlServer (and Postgres) adds that promise beyond the standard.
  • @Jon Thanks for clarifying. Let me rephrase. In SQL Server, truncate is transactional.