>>>>>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 ?
