Simeon Simeon - 4 months ago 10
SQL Question

How to clear a database with foreign key constraints?

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; } }
}

Answer

There are several solutions.

  1. 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.

  2. 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.

  3. You can also setup cascade deletion, but I strongly adwise against it since you can occasionally delete data you would not like to delete.