0

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.

0
0

Your goal should be achievable much more simply with a foreign key constraint between your tables.

If you create foreign keys between your Purchases / 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.

The 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.

In fact, 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.

3
  • Hello! You didn't get my question it's not as simple as defining Relation between 2 Tables. Jun 5 at 13:33
  • 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. Jun 5 at 13:44
  • 1
    @sandeepbj That then does sound like a potential design flaw. You would need to either use two fields in your Transactions table, one for each related table, or two junction tables, such that you can create the appropriate foreign key relationships. Those would be proper designs, and are fairly common ways to design Purchases, Sales, and Transactions. Please update your post with this information, and better yet your actual table schema with information on how they currently relate, to improve your post's potential.
    – J.D.
    Jun 5 at 16:37
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.