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.