I am using Visual Basic with MS Sql Server Database for my WinForms Application.
I have 3 Tables – Purchases, Sales, Transactions (for payment/receipt)
In the Transactions table I am using Purchase Table’s RowId OR Sales Table’s RowId as a Reference to determine the payment belongs to which purchase/sale record.
Now after entering records in Transaction table, I dont’t want to let Delete Purchase/Sale entry if there RowId is used in Transactions table records.
What I have tried is RowId’s of both Purchase & Sale Tables are Unique values so Created 1 view named PurchaseSaleIds which Unions values of both table, now I have PurchaeSaleIds as Primary Key Table (View) & Transactions as Foreign Key Table, however it is not allowed in SQL Server to set foreign key relation between View & Table.