0

i want to clear data from this table from entity framework

db2.Database.ExecuteSqlCommand("TRUNCATE TABLE [destDepartments]");

but i have this error

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

so how i can do this from EF?

Gehad
  • 17
  • 7
  • EF or not, `TRUNCATE` is not allowed here for the reasons mentioned in the error. Use `DELETE` instead as that will ensure you don't break referential integrity. – Dan Guzman Oct 18 '18 at 10:04
  • You couldn't do it even if you ran the sql command directly in SSMS. to be able to truncate a table, it must have no tables referencing it with foreign keys. – Zohar Peled Oct 18 '18 at 10:04
  • You want to delete all the data from this table? The error says that the data in this able is being referenced by some other table via ForeignKey. So you first need to clear the other table. – Chetan Oct 18 '18 at 10:06
  • @ChetanRanpariya i already delete the other table in the previous line in my code but i don't know what the wrong – Gehad Oct 18 '18 at 10:08
  • https://stackoverflow.com/questions/253849/cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint – Chetan Oct 18 '18 at 10:11
  • Instead of truncating the table why not use `DELETE FROM destDepartments` command.? – Chetan Oct 18 '18 at 10:12
  • IT WORKS WELL!. – Gehad Oct 18 '18 at 10:23

2 Answers2

2

There are restrictions on when you can use TRUNCATE TABLE. One of them is that the table cannot be involved in foreign key relationships with other tables in the database. Use DELETE FROM [destDepartments] instead.

TRUNCATE TABLE and DELETE FROM are not functionally equivalent but the latter will allow you to clear a table that is related to other tables in the database (provided referential integrity is maintained).

cf_en
  • 1,661
  • 1
  • 10
  • 18
0

Make sure to get rid of any references before deleting the table.

Callam Woolgar
  • 1
  • 2
  • It's not that there are related records, it's that there is a foreign key relationship at all. Even if there were no related records, `TRUNCATE TABLE` would error because of the foreign key. – cf_en Oct 18 '18 at 10:09