locked
data loss in webmatrix RRS feed

  • Question

  • User1160188248 posted

    hello,

    I made an application using webmatrix and now the database have more than 15,000 records...

    Recently i notice that rows have been deleted from the tables. I'm sure that no one deleted this, also i don't have an option to delete records in the web application.

    Hope you can help me solve this problem. thnx.

    Friday, January 16, 2015 6:10 AM

Answers

  • User-760709272 posted

    If you have a relationship between two tables, like "Order" and "OrderDetail" such that OrderDetail.OrderID relates to a row in Order, you can configure that relationship such that if an Order is deleted, all related OrderDetail items are deleted also.  Cascading deletes can be quite dangerous if you don't pay attention.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 16, 2015 9:47 AM

All replies

  • User-760709272 posted

    Do you have any referential integrity with cascading delete in your database?  Rows don't get deleted without something deleting them, if there was an issue where data randomly got lost I think it would be a pretty major issue that we'd all know about.

    Friday, January 16, 2015 6:19 AM
  • User379720387 posted

    sql ce has a nasty habit of not putting new records of the bottom of the table, apparently in an effort to sow maximum confusion and waste coders' time.

    It is entirely conceivable that something similar is going on with deleting too.

    Have you done a query to verify?

    Is your query really doing the thing it is supposed to be doing.

    Friday, January 16, 2015 9:17 AM
  • User379720387 posted

    @aidyf...

    What is cascading delete?

    Do you have any referential integrity with cascading delete in your database?

    Friday, January 16, 2015 9:20 AM
  • User-760709272 posted

    If you have a relationship between two tables, like "Order" and "OrderDetail" such that OrderDetail.OrderID relates to a row in Order, you can configure that relationship such that if an Order is deleted, all related OrderDetail items are deleted also.  Cascading deletes can be quite dangerous if you don't pay attention.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 16, 2015 9:47 AM
  • User379720387 posted

    How does on configure a cascading delete? I do have these relationships set up, but I am always forced to delete step by step.

    Is that a feature of SQL CE?

    Friday, January 16, 2015 9:55 AM
  • User1853794821 posted

    Try this in a query window.  It drops any current foreign key constraint on the target "child" table (assuming one exists) and then re-creates it with the DELETE Cascade.  This would then cause the deletion of the referenced "parent" row to cascade its delete to all the "child" rows having "DELETE CASCADE" foreign keys referencing the parent table :

    ALTER TABLE [dbo].[<child table name>] DROP CONSTRAINT [<name of FK Constraint currently in effect>]
    GO
    
    ALTER TABLE [dbo].[<child table name>]    CONSTRAINT [<name of FK Constraint currently in effect>] FOREIGN KEY([<parent table's PK>])
    REFERENCES [dbo].[<parent table name>] ([ID])
    ON DELETE CASCADE
    GO


    --the following should work for your situation. Given a child table of transactions with a foreign key (FK_TXN_Client) to clients and you want the database to delete all transactions for the client when a client is deleted:
    ALTER TABLE Transactions DROP CONSTRAINT FK_txn_Client
    GO

    ALTER TABLE Transactions   ADD  CONSTRAINT FK_txn_Client FOREIGN KEY(ClientId)
    REFERENCES Clients (ClientId)
    ON DELETE CASCADE
    GO

    Friday, January 16, 2015 10:54 AM