March 18, 2010 at 4:11 am
I am trying to truncate table and get an error message can't truncate table because of the foreighn key,can I disable temporary?
March 18, 2010 at 4:33 am
You have to drop them. A disabled foreign key is still a foreign key and a table referenced by a fk cannot be truncated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2010 at 4:38 am
If you disable primary key of unique index to which foreign key references; foreign key will get disabled. Not sure this will allow you to use TRUNCATE command.
Alternately-
You can script the foreign key. then drop it. truncate the table and again apply it.
March 18, 2010 at 5:07 am
GilaMonster (3/18/2010)
A disabled foreign key is still a foreign key and a table referenced by a fk cannot be truncated.
Unless it is a self-reference 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2010 at 5:34 am
what i should I have 4 tables, 2 tables were truncated by table 3 and 4 giving this error
March 18, 2010 at 5:43 am
Krasavita (3/18/2010)
what i should I have 4 tables, 2 tables were truncated by table 3 and 4 giving this error
It would help explain the problem if you post the full CREATE TABLE statements for each table, including any FOREIGN KEY constraints.
Gail already explained the reason for the error and what to do about it. Can't provide you with the SQL statements to do what you need without seeing the definitions...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2010 at 5:54 am
e
March 18, 2010 at 6:02 am
That's 2 out of the 4 tables.
Please just post the script in the forum directly rather than in attached word docs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2010 at 6:11 am
Example based on those two tables:
ALTER TABLE dbo.OrderFormHeader
DROP CONSTRAINT [FK_OrderFormHeader_OrderGroup];
TRUNCATE TABLE dbo.OrderGroup;
TRUNCATE TABLE dbo.OrderFormHeader;
ALTER TABLE dbo.OrderFormHeader
WITH CHECK
ADD CONSTRAINT [FK_OrderFormHeader_OrderGroup]
FOREIGN KEY (ordergroup_id)
REFERENCES dbo.OrderGroup (ordergroup_id);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2010 at 6:13 am
Thank you very much
March 18, 2010 at 11:49 am
I still get this error:
Cannot truncate table 'OrderFormHeader' because it is being referenced by a FOREIGN KEY constraint
March 18, 2010 at 1:37 pm
you can disable the constraint
ALTER TABLE 'table name' NOCHECK CONSTRAINT all
ALTER TABLE 'table name' DISABLE TRIGGER all
Thanks,
Nikul
March 18, 2010 at 1:41 pm
Nik Desai (3/18/2010)
you can disable the constraintALTER TABLE 'table name' NOCHECK CONSTRAINT all
ALTER TABLE 'table name' DISABLE TRIGGER all
Disabling the constraint is not sufficient to allow the table to be truncated. The constrain has to be dropped.
Why are you suggesting disabling all triggers as well? Triggers don't impact a truncate.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2010 at 1:48 pm
It should be more like this
alter table TableName Drop constraint FK_Name
Then perform your truncate
Then this
alter table TableName
Add constraint FK_Name Foreign Key (FieldName) References OtherTable (FieldName)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2010 at 11:27 am
Thank you it worked
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply