This is for Visual Studio 2010 and SQL Server Compact 3.5
I have two tables: stockNames and stockData. StockName has only one column. stockData has several columns with one of them being the same column as in stockNames. I'm using the single column stockNames to quickly read and update a listbox on my form.
I want to be able to erase a stockName entry and automatically delete all of its entries in the stockData table. I read that I need to create a relationship but when I got to the screen to do it ... I got lost. There is "update rule", "delete
rule" with 3 options in each. I found a page that seems to explain every entry but ... I guess I'm a bit slow :-) ... I did not understand how to set up a cascade delete. Could someone point me in the right direction please?
Here is what the data looks like (dummy data shown). If I delete TSM from stockNames the 3 entries in stockData should be deleted as well:
stockNames table: column name: stock:
stockData table: column names: ask, bid, volume, date, time, stock, delta, hi, lo
Please refer to the following steps:
1. Add PRIMARY KEY constraint to the table "StockNames". For example:
ALTER TABLE stockNames
ADD CONSTRAINT PK_stockNames
PRIMARY KEY (stock)
2. Add FOREIGN KEY constraint with delete cascading to the table "StockData":
ALTER TABLE stockData
ADD CONSTRAINT FK_stockNames
FOREIGN KEY (stock) REFERENCES stockNames(stock) ON DELETE CASCADE