How to truncate table

1

Hi
How to truncate table with foreign key ralationship

2

I don't think you can, that is how it design. I haven't try to disable the FK and tried tho, maybe worth to try. What I would say is since you got FK in-place, might worth to use delete statement and delete them by batch, which can enforce the FK and make sure nothing breaks.

3

You cannot truncate a table which has an FK constraint on it, Because TRUNCATE TABLE is a DDL command, it cannot check to see whether the records in the table are being referenced by a record in the child table.

4

Here is a script that helps get around this situation. It generates the DROP and CREATE FK statements for the given table and, optionally, scripts the actual truncate.[code]-----------------------------------------------------------------------------
--- Given a table name, return all the Foreign Keys that reference it, their
--- columns and referenced table and columns.

declare
@table sysname = 'MyTable',
@truncate bit = 0

declare
@PKey int,
@MaxPkey int,
@fkName sysname,
@Cols varchar(1000),
@RefCols varchar(1000),

@OnDelete	nvarchar(11),
@OnUpdate	nvarchar(11),

@is_not_for_replication	bit,
@is_disabled	bit

declare @FKs table (
fkName sysname,
tblName sysname,
colName sysname,
column_id int,
refTblName sysname,
refColName sysname,

OnDelete	nvarchar(11),
OnUpdate	nvarchar(11),

is_not_for_replication	tinyint,
is_disabled	tinyint
)

declare @DropCreates table (
PKey int identity(1, 1),

fkName		sysname,
tblName		sysname,
refTblName	sysname,

dropStmt	varchar(500),
createStmt	varchar(500)
)

--- Get raw foreign key details

insert into @FKs (
fkName,
tblName,
colName,
column_id,
refTblName,
refColName,
OnDelete,
OnUpdate,
is_not_for_replication,
is_disabled
)
select
fk.Name fkName,

parent.Name		tblName,
fkcc.Name		colName,
fkc.constraint_column_id,

reference.Name	refTable,
refCol.Name		refColumn,

case delete_referential_action
	when 0 then N'NO ACTION'
	when 1 then N'CASCADE'
	when 2 then N'SET NULL'
	when 3 then N'SET DEFAULT'
	else N'<Unknown>'
	end,

case update_referential_action
	when 0 then N'NO ACTION'
	when 1 then N'CASCADE'
	when 2 then N'SET NULL'
	when 3 then N'SET DEFAULT'
	else N'<Unknown>'
	end,

fk.is_not_for_replication,

fk.is_disabled

from
sys.foreign_keys fk
inner join
sys.foreign_key_columns fkc
on fkc.constraint_object_id = fk.object_id
inner join
sys.objects parent
on
fk.parent_object_id = parent.object_id
inner join
sys.columns fkcc
on fkcc.object_id = parent.object_id
and fkcc.column_id = fkc.parent_column_id

inner join
sys.objects reference
on
reference.object_id = fk.referenced_object_id

inner join
sys.columns refCol
on refCol.object_id = reference.object_id
and refCol.column_id = fkc.referenced_column_id
where
reference.Name = @table
order by
parent.Name,
fk.Name,
fkc.constraint_column_id

--/**/select * from @FKs

--- Extract unique FKs

insert into @DropCreates (
fkName,
tblName,
refTblName,
dropStmt,
createStmt
)
select distinct
fkName,
tblName,
refTblName,
'',
''
from
@FKs

select
@PKey = 1,
@MaxPKey = max(PKey)
from
@DropCreates

--/**/select * from @DropCreates

--- Generate the DROP and CREATE statements

while (@PKey <= @MaxPKey)
begin
select
@fkName = fkName
from
@DropCreates
where
PKey = @PKey

---------------------------------------------

select
	@Cols = '',
	@RefCols = ''

update @FKs
set	@Cols += colName + ', ',
	@RefCols += refColName + ', '
where
	fkName = @fkName

select
	@OnDelete	= min(OnDelete),
	@OnUpdate	= min(OnUpdate),

	@is_not_for_replication	= min(is_not_for_replication),
	@is_disabled	= min(is_disabled)
from
	@FKs
where
	fkName = @fkName

---------------------------------------------

update @DropCreates
set	DropStmt	= 'ALTER TABLE dbo.[' + tblName + '] DROP CONSTRAINT [' + @fkName + '];',
	CreateStmt	= 'ALTER TABLE dbo.[' + tblName + '] ADD CONSTRAINT [' + @fkName + '] ' + char(13) + char(10) +
		char(9) + 'FOREIGN KEY (' + left(@cols, len(@Cols) - 1) + ') ' + char(13) + char(10) +
		char(9) + 'REFERENCES dbo.[' + refTblName + '] (' + left(@RefCols, len(@RefCols) - 1) + ') ' + char(13) + char(10) +
		char(9) + 'ON DELETE ' + @OnDelete +  char(13) + char(10) +
		char(9) + 'ON UPDATE ' + @OnUpdate + 
		case
			when @is_not_for_replication = 1
			then char(13) + char(10) + char(9) + 'NOT FOR REPLICATION'
			else ''
			end +
		';'
where
	fkName = @fkName

---------------------------------------------

set @PKey += 1;

end

--- Return the results

select a.SqlStmt
from (
select 1 ord, DropStmt SqlStmt
from @DropCreates
--order by DropStmt

union all

select 2, ''

union all

select 3 ord, case @truncate
	when 1 then 'truncate table dbo.' + @table + ';'
	else ''
	end SqlStmt

union all

select 4, ''

union all

select 5, CreateStmt SqlStmt
from @DropCreates
--order by DropStmt
)a

order by ord[/code]HTH