June 6, 2013 at 11:25 pm
Hi.
could not truncate table due to FK constraints
Table defination
/****** Object: Table [dbo].[DiagHistory] Script Date: 06/07/2013 10:49:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DiagHistory](
[DiagHistoryKey] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
[TypeID] [uniqueidentifier] NOT NULL,
[DescriptionID] [uniqueidentifier] NOT NULL,
[Description] [ntext] NOT NULL,
[DiagSourcesKey] [uniqueidentifier] NOT NULL,
[DiagDomainKey] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_DiagHistory] PRIMARY KEY CLUSTERED
(
[DiagHistoryKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[DiagHistory] WITH CHECK ADD CONSTRAINT [FK_DiagHistory_DiagDescriptionIDs] FOREIGN KEY([DescriptionID])
REFERENCES [dbo].[DiagDescriptionIDs] ([DescriptionID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DiagHistory] CHECK CONSTRAINT [FK_DiagHistory_DiagDescriptionIDs]
GO
ALTER TABLE [dbo].[DiagHistory] WITH CHECK ADD CONSTRAINT [FK_DiagHistory_DiagDomain] FOREIGN KEY([DiagDomainKey])
REFERENCES [dbo].[DiagDomain] ([DiagDomainKey])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DiagHistory] CHECK CONSTRAINT [FK_DiagHistory_DiagDomain]
GO
ALTER TABLE [dbo].[DiagHistory] WITH CHECK ADD CONSTRAINT [FK_DiagHistory_DiagSources] FOREIGN KEY([DiagSourcesKey])
REFERENCES [dbo].[DiagSources] ([DiagSourcesKey])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DiagHistory] CHECK CONSTRAINT [FK_DiagHistory_DiagSources]
GO
ALTER TABLE [dbo].[DiagHistory] WITH CHECK ADD CONSTRAINT [FK_DiagHistory_DiagTypeIDs] FOREIGN KEY([TypeID])
REFERENCES [dbo].[DiagTypeIDs] ([TypeID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DiagHistory] CHECK CONSTRAINT [FK_DiagHistory_DiagTypeIDs]
GO
ALTER TABLE [dbo].[DiagHistory] ADD CONSTRAINT [DF_DiagHistory_DiagHistoryKey] DEFAULT (newid()) FOR [DiagHistoryKey]
GO
I have tried as below script but its not working
alter table [dbo].[DiagHistory] nocheck constraint DF_DiagHistory_DiagHistoryKey
go
truncate table diaghistory
go
alter table [dbo].[DiagHistory] check constraint DF_DiagHistory_DiagHistoryKey
go
could you suggestion me for single batch script to truncate data?
Error
Msg 11415, Level 16, State 1, Line 1
Object 'DF_DiagHistory_DiagHistoryKey' cannot be disabled or enabled. This action applies only to foreign key and check constraints.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'diaghistory' because it is being referenced by a FOREIGN KEY constraint.
Msg 11415, Level 16, State 1, Line 1
Object 'DF_DiagHistory_DiagHistoryKey' cannot be disabled or enabled. This action applies only to foreign key and check constraints.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.
June 6, 2013 at 11:58 pm
As documented in BOL: TRUNCATE TABLE (Transact-SQL)
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.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. For more information, see CREATE TRIGGER (Transact-SQL).
Truncating Large Tables
Microsoft SQL Server has the ability to drop or truncate tables that have more than 128 extents without holding simultaneous locks on all the extents required for the drop.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 7, 2013 at 12:06 am
DELETE statement will be hang due to 76554566 total records.. any suggestion Pls.
June 7, 2013 at 12:50 am
ananda.murugesan (6/7/2013)
DELETE statement will be hang due to 76554566 total records.. any suggestion Pls.
You can try the below steps
1. DELETE or TRUNCATE the data from all tables that are dependent on this main table
2. Remove the Foreign Key constraints temporarily on these tables
3. TRUNCATE the data in the main table now
4. Add the Foreign Key constraints back on these tables
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply