Hi all

I have a transactions records table called uOSStock. Any record of
this table will contain adjustment details between 2 tables namely
sQuotationItems and sProformaInvItems, sProformaInvItems and
sPackingList, and so on.

My sole aim is to automate the deletion of corresponding uOSStock
entries applying the Delete Rule Cascade, when records from
sProformaInvItems and sPackingListItems tables are deleted.

The basic idea is that when a proforma invoice items are entered they
are set of against the quotation items (I have got the quotation nos.)
on a FIFO basis. Further this proforma inv. quantities are set of
against packing list items.

Thus after a quotation is entered and it's corresponding proforma inv.
is entered, uOSStock will contains records as follows:

Record #1: iID=1 iFKID=5 cFKName=SPROFORMAINVITEMS iDocID=453
iItemID=568 bQty=50
Record #2: iID=2 iFKID=6 cFKName=SPROFORMAINVITEMS iDocID=454
iItemID=742 bQty=25

Further when a packing list is entered and is adjusted against the
previsouly entered proforma inv., uOSStock will contains more records
as follows:

Record #3: iID=3 iFKID=35 cFKName=SPACKINGLISTITEMS iDocID=5
iItemID=568 bQty=45
Record #4: iID=4 iFKID=36 cFKName=SPACKINGLISTITEMS iDocID=6
iItemID=742 bQty=20

Where iID is the PK. iFKID is the shared FK between the tables
sProformaInvItems and sPackingListItems. cFKName is the name of the
table that generated the records. iDocID will contain FK ref. to
tables sQuotationItems and sProformaInvItems respectively in relation
to iFKID as you can notice from iFKID and iDocID repetition.

Now I have created 2 constraints, namely FK_SPROFORMAINVITEMS and
FK_SPACKINGLISTITEMS, on iFKID against FK Tables sProformaInvItems and
sPackingListItems resp., with Update Rule as None and Delete Rule as
Cascade.

But when I add a record to uOSStock table from the sPackingListItems
tables logic I get an error stating:
---------------------------
violation of FOREIGN KEY constraint "FK_SPROFORMAINVITEMS" on table
"UOSSTOCK"
---------------------------
and vice-versa.

I have thought about seperating this uOSStock table into 2 tables for
eg. uOSQuotProInvStock and uOSProInvPackListStock but am not sure if
this will have any reporting pitfalls as the application progresses.

Maybe the user asks for a progressive report which shows the history
of adjustments (see qty 50 is 45 and 25 is 20).

Please advise if multiple constraints as mentioned above are possible?
My sole aim is automation of deletion of the uOSStock entries when a
corresponding iFKID (I have just noted that I will somehow have to
have cFKName field also interacting at the time of deletion).

Please advise on the above or if there is a better way to manage
adjustments of qty. between stages of transactions.

Sorry about such a lengthy post but I wanted to explain my aim as best
as possible.

Regards
Bhavbhuti