Last post Jan 16, 2015 10:54 AM by rrrsr7205
Jan 16, 2015 06:10 AM|alem.moataz|LINK
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.
Jan 16, 2015 06:19 AM|AidyF|LINK
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.
Jan 16, 2015 09:17 AM|wavemaster|LINK
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.
Jan 16, 2015 09:20 AM|wavemaster|LINK
What is cascading delete?
Do you have any referential integrity with cascading delete in your database?
Jan 16, 2015 09:47 AM|AidyF|LINK
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.
Jan 16, 2015 09:55 AM|wavemaster|LINK
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?
Jan 16, 2015 10:54 AM|rrrsr7205|LINK
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>]
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