Hi
How to truncate table with foreign key ralationship
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.
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.
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