Mitchell Peek wrote:

> Mitchell Peek wrote:
>
>
> The other issue that may or may not be related....
>
>
> Given the same 2 tables.
>
> CREATE TABLE TILL (
> STORE_ID BIGINT NOT NULL ,
> ID BIGINT NOT NULL ,
> CASHIER_ID BIGINT NOT NULL ,
> COMPUTEDBAL NUMERIC(18,2) ,
> OUTTIME TIMESTAMP NOT NULL,
> INTIME TIMESTAMP,
> ENDINGBAL NUMERIC(18,2),
> TAX_TOTAL NUMERIC(18,2)
> );
>
> ALTER TABLE TILL ADD CONSTRAINT PK_TILL PRIMARY KEY (STORE_ID, ID);
> ALTER TABLE TILL ADD CONSTRAINT FK_TILL_1 FOREIGN KEY (STORE_ID)
> REFERENCES STORE (ID);
> ALTER TABLE TILL ADD CONSTRAINT FK_TILL_3 FOREIGN KEY (STORE_ID,
> CASHIER_ID) REFERENCES PERSON2TYPE (STORE_ID, ID);
>
>
> CREATE TABLE TRANSACT (
> STORE_ID BIGINT NOT NULL,
> ID BIGINT NOT NULL ,
> TILL_ID BIGINT NOT NULL ,
> AMOUNT_TENDERED NUMERIC(18,2) NOT NULL ,
> ITEM_TOTAL NUMERIC(18,2) NOT NULL ,
> TAX NUMERIC(18,2) NOT NULL
> );
>
> ALTER TABLE TRANSACT ADD CONSTRAINT PK_TRANSACT PRIMARY KEY (STORE_ID, ID);
>
> ALTER TABLE TRANSACT ADD CONSTRAINT FK_TRANSACT_2 FOREIGN KEY (STORE_ID,
> TILL_ID) REFERENCES TILL (STORE_ID, ID);
>
> ALTER TABLE TRANSACT ADD CONSTRAINT FK_TRANSACT_3 FOREIGN KEY (STORE_ID)
> REFERENCES STORE (ID);
>
>
> the following delphi code is run..
>
> dm.QryPOST.Close;
> dm.QryPOST.SQL.Text :=
> 'INSERT INTO TRANSACT (STORE_ID, ID, TILL_ID, ITEM_TOTAL, TAX, AMOUNT_TENDERED )' +
> ' VALUES '+
> (' FStoreID + ',' + FTransactID + ',' +FTillID +',' + FSubTotal + ',' + FTax + ', ' +
> FAmtTend + ')';
>
> try
> dm.QryPOST.ExecSQL;
> except
> on E: Exception do
> begin
> MessageDlg('Error writing Main Transaction #'+IntToStr(FTransactID) +
> 'for TillID #'+IntToStr(FTillID)+' : ' +E.Message, mtError, [mbOK], 0);
> raise;
> end;
> end;
>
> I have actually gotten an error that says this insert failed because of a FK violation, specifically. FK_TRANSACT_2. Yet, in the error message, I show the value of FTILLID, and it is a valid number of an existing row in TILL. Retrying the operation works. The TILL row having ID in FTILLID had been in the table for hours.
>
> related??? I don't think so, but....
>
> Admittedly, I have not ruled out that the component somehow failed to pass through the SQL properly. but I would think that unlikely.

Hello,

I think there might be 2 possible causes:

1. Master ID record is inserted but not posted;
2. Master ID inserted/posted in a transaction isolated from your detail
insert query
Are you using FIBplus?

HTH

Radu