If a transaction can match many invoices, and
an invoice can match many transactions, there is
no direct way to "lock" the rows so that they
cannot be used any more once already used.
For example if you match 2 invoices to 3
transactions, each transaction will refer to 2
invoices, and each invoice will refer to 3
There is no direct way to tell SQL Server:
"Man, we're done, don't allow to match these rows
any more!". And that's becasue you cannot define a
rule for it.
Once you know that there is no possibility to
create this rule the easiest way to solve your
problem is to implemente it using your application
logic, and with this DB design:
- add a
Matched bit column on each
- and a classical M-N intermediate table that
has a primary key composed of the primary keys of
both invoices and transactions
And then, the rest of the job must be done from
your application logic:
- create the entries on the intermediate
- set the
- don't offered the users to match invoices or
transactions that are already matched (by using
this flag column)
This is a simple, easy to implemente and clear
solution. Follow the KISS principle.
Creating a number for each matching group, i.e.
a third identity column on the intermediate table,
can help you when reverting the change or querying
the matching group, but it's not necessary.