1

I am trying to TRUNCATE a table but I am getting this error:

Cannot truncate a table 'ALOT_OF_USELESS_LOGS' because it is being referenced by a FOREIGN KEY constraint.

However, I have already truncated all the table where it is referenced EXEC sp_fkeys 'ALOT_OF_USELESS_LOGS' and they all truncated successfully but over this table I am still getting this error.

How can I let this go, ideally without dropping constraints?

Maven
  • 14,587
  • 42
  • 113
  • 174
  • You can't do it without dropping the constraints. It doesn't matter that the other tables are empty or (if the columns are nullable) no rows have any active foreign key relationships with this table. They key point that `TRUNCATE` wants is that it doesn't want to have to perform *any* foreign key checks at all. – Damien_The_Unbeliever Nov 28 '14 at 07:04
  • duplicated question [Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?](http://stackoverflow.com/questions/253849/cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint#253931) – Adriaan Stander Nov 28 '14 at 07:05

2 Answers2

4

Restrictions of Truncate (source: MSDN)

You cannot use TRUNCATE TABLE on tables that:

  1. Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
  2. Participate in an indexed view.
  3. Are published by using transactional replication or merge replication.

So you cannot use truncate command over a table if referenced by a Foreign Key

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
1

Write down 

 show create table TABLENAME

This will show you all the table schema, check the constraint that you have created, remove it and delete it.

Danyal Sandeelo
  • 12,196
  • 10
  • 47
  • 78