Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

truncate error - foreign key and check constraints Expand / Collapse
Author
Message
Posted Thursday, June 6, 2013 11:25 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:55 PM
Points: 1,076, Visits: 3,054
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.

Post #1460957
Posted Thursday, June 6, 2013 11:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 5:03 AM
Points: 6,748, Visits: 8,544
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #1460965
Posted Friday, June 7, 2013 12:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 8:55 PM
Points: 1,076, Visits: 3,054
DELETE statement will be hang due to 76554566 total records.. any suggestion Pls.
Post #1460970
Posted Friday, June 7, 2013 12:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
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



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1460976
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse