How to clear a database with foreign key constraints?

Question

I want my seed method to first clear/drop the database and get rid of all the old data, however

context.Database.ExecuteSqlCommand("TRUNCATE TABLE [Purchases]");
context.Database.ExecuteSqlCommand("TRUNCATE TABLE [Invoices]");

gives me

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

because entries in Purchases are dependent on entries in Invoices. How can I clear all the data via the seed method?

edit: These are the relevant models:

public class Invoice
{
    //Primary Key
    public int InvoiceID { get; set; }

    //Misc. info
    public DateTime CreationDate { get; set; }
    public DateTime DeadlineDate { get; set; }
    public string ReceiverName { get; set; }

    //Order details
    public virtual List<Purchase> Purchases { get; set; }

    //Auto-calculated property
    [DataType(DataType.Currency)]
    public float TotalCost { get; set; }

    //Invoice author info
    public string AuthorName { get; set; }
    public string AuthorID { get; set; }
}


public class Purchase
{
    public int PurchaseID { get; set; }

    public string ProductDescription { get; set; }

    public float SinglePrice { get; set; }

    public float Amount { get; set; }

    public float TotalPrice { get { return Amount * SinglePrice; } }
}

lorond · Accepted Answer · 2016-07-22T20:46:34.600

There are several solutions.

If you want to clean entire database, the simplest solution is just to recreate whole database. More complex solution is to drop foreign key, clean database and create foreight keys agan. This can be automated. Doing this manuallynot is not a good idea. Much better to proceed to next option.
Knowing your database structure you can just drop data the the order, that will not violate foreign key constraints. This also can be automated, but with more efforts. However is it very easy to write TRUNCEs in valid order manually.

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

In this case first of all determine what table referencing table Purchases, and clean data in that table before truncating Purchases.

Also there is no way to drop data without dropping (or marking WITH NOCHECK) foreign keys if you have cycle references.
You can also setup cascade deletion, but I strongly adwise against it since you can occasionally delete data you would not like to delete.

Cascaded delete worked, thanks. I didn't try it before because it wouldn't let me do a for loop on a DbSet, and I thought you couldn't remove members of the queried collection in a foreach loop but I was wrong apparently. Cheers. — Simeon, Jul 22 '16 at 23:30

score 1 · Answer 2 · answered Jul 22 '16 at 20:36

1

Since the seed method is called after updating the database I don't think your method will work. What I have done in the past is to execute:

Update-Database -TargetMigration 0

That will reset the database to the first migration. Then execute:

Update-Database

answered Jul 22 '16 at 20:36

agriffin

541
5
10

ah good point, I thought he needed to do a truncate on a production DB, I didnt realize it was just to clean slate the EF – RJB Jul 22 '16 at 20:40
This will execute downgrade methods, but if data was inserted outside the migrations, it will not deleted. – lorond Jul 22 '16 at 20:44
1

One of those `Down` methods will include `DROP TABLE`.... which will delete any/all data..... – RJB Jul 22 '16 at 20:45

score 0 · Answer 3 · answered Jul 22 '16 at 20:33

0

You will need to clear your tables in order of your foreign key constraints. If your Purchases table is referenced by a foreign key constraint from Invoices you will need to first truncate Invoices.

answered Jul 22 '16 at 20:33

Collin Stevens

737
9
19

score 0 · Answer 4 · edited May 23 '17 at 12:08

If you're just trying to clear out data to re-seed, agriffin is right, just use -Target-Migration:0 to reset the EF.

If you're trying to Truncate on production data, you'll need to drop the foreign key constraints first, and then re-add them after. There's not really any way around it.

But the script in my other answer here will quickly generate the ALTER TABLE SQL you need to add before and after your truncate. I suggest doing it all as part of a stored procedure:

ALTER TABLE [dbo].[Purchases] DROP CONSTRAINT....

TRUNCATE TABLE [Purchases];

ALTER TABLE [dbo].[Purchases] ADD CONSTRAINT....

How to clear a database with foreign key constraints?

4 Answers4