>>>>>>Hi,
>>>>>>
>>>>>>I am going through a testing phase of converting a VFP database to SQL Server database. And I expect to have to truncate SQL tables maybe several times as I fix problems. But what happens is on the first truncate (even when ALL tables of SQL Server have no records) I get message that "truncate cannot be executed because table has FOREIGN KEY constraints". I understand that it is logical not to allow breaking of a foreign key constraint but the tables have no records/rows. Therefore at this point there should not be (at least IMHO) a reason not to allow truncate. What am I missing? TIA.
>>>>>
>>>>>You're not missing anything - that's the way it is: http://msdn.microsoft.com/en-us/library/ms177570.aspx :
>>>>>"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."
>>>>
>>>>Ok; I will have to use DELETE. I would prefer to TRUNCATE since it resets the Identity column back to 0 too. But it is not such a big deal. Thank you for your help.
>>>
>>>We use this way. Simple Example:
>>>

>>>        ALTER TABLE dbo.DNST DROP CONSTRAINT FK_DNST_TAB_DOD
>>>        ALTER TABLE dbo.HON  DROP CONSTRAINT FK_HON_DNST
>>>
>>>        TRUNCATE TABLE dbo.DNST
>>>
>>>        ALTER TABLE dbo.DNST WITH NOCHECK ADD CONSTRAINT
>>>              FK_DNST_TAB_DOD FOREIGN KEY (TYPEDOD) REFERENCES dbo.TAB_DOD (KOD)
>>>
>>>        ALTER TABLE dbo.HON WITH NOCHECK ADD CONSTRAINT
>>>              FK_HON_DNST FOREIGN KEY (DNST) REFERENCES dbo.DNST (KOD)
>>>

>>
>>Thank you. I am sure your approach will work. But I would have to hard-code all FOREIGN KEY constraints for each table in order to TRUNCATE any table. Seems like too much work. But thank you for sharing.
>
>If you need to clean out the whole database maybe it would be just as simple to delete it and re-create from script ?

This is what I do when I need to clean out the entire database. But I thought there could be a case when I would need to TRUNCATE one or two or a few tables. So far I have decided that DELETE is the next best approach.
Thank you.