All we need is an easy explanation of the problem, so here it is.
I am using Visual Basic with a Microsoft 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 don’t want to let Delete Purchase/Sale entry if there RowId is used in Transactions table records.
What I have tried is RowIds of both Purchase & Sale Tables are Unique values so created 1 view named PurchaseSaleIds which Unions values of both table, now I have PurchaseSaleIds 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.
Table Purchases have BillID primary key column, Table Invoices have InvID primary key column and under Transaction table I have AgainstBillId Column which will store ID number between BillID Or InvID.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Your goal should be achievable much more simply with a foreign key constraint between your tables.
If you create foreign keys between your
Sales tables and the
Transactions table, you can specify the
ON DELETE action to
NO ACTION as specified in the Microsoft BOL for Primary and Foreign Key Constraints – Cascading Referential Integrity.
NO ACTION will prevent
DELETEs from occurring on the parent table when there is a related record by the foreign key in the child table:
The Database Engine raises an error and the delete or update action on the row in the parent table is rolled back.
NO ACTION is the default, so just declaring a foreign key without specifying an
ON DELETE clause is enough to prevent a parent row from deleting if it is already being referenced.
I found Two alternatives to solve this problem.
1st traditional way is Before Deleting Purchase Or Sale Entry I will check whether the respective BillID or InvID is present in Transaction Table AgainstBillId Column, if it is then will cancel Delete steps.
2nd way is as I have told I created 1 View in Database which UNION‘s both BillID & InvID, so I will have Single column with all IDs from Purchase & Sale Tables, Now the creation for Foreign key is Not supported with View in Database So will add it it DataSet Designer in Visual Studio where It is supported.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂