CODEWITHSUNDEEP
c#sql
Simeon
Simeon

Reputation: 243

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

Upvotes: 2

Views: 2610

Answers (4)

lorond
lorond

Reputation: 3896

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.

Upvotes: 1

RJB
RJB

Reputation: 2103

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

Upvotes: 0

agriffin
agriffin

Reputation: 541

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

Upvotes: 1

Collin Stevens
Collin Stevens

Reputation: 817

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.

Upvotes: 0

Related Questions