This should be simple... (FB 1.5 Dialect 3)

I am trying to add a master and detail record in the same transaction, but when I insert the detail record, I get a FK exception indicating the detail ID is not legal. But I should be able to do that, right?

The details are below, but what am I doing wrong?

Thank you,

Ed Dressel

The (not quite full) DLL for the tables and the monitor SQL is below:

******************************************
The master table is ClientInfo
******************************************
CREATE TABLE CLIENTINFO (
CLIENT_ID DM_KEY /* DM_KEY = INTEGER NOT NULL */,
CREATED_DATETIME DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
UPDATED_DATETIME DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
EMAIL DM_VARCHAR50 /* DM_VARCHAR50 = VARCHAR(50) */,
EMAIL_UPPER DM_VARCHAR50 /* DM_VARCHAR50 = VARCHAR(50)
);

ALTER TABLE CLIENTINFO ADD CONSTRAINT PK_CLIENTINFO PRIMARY KEY (CLIENT_ID);


******************************************
The detail table is ClientActionLog
******************************************

CREATE TABLE CLIENTACTIONLOG (
CLIENTACTIONLOG_ID DM_KEY /* DM_KEY = INTEGER NOT NULL */,
EVENT_DATETIME DM_DATETIME /* DM_DATETIME = TIMESTAMP */,
CLIENT_ID DM_KEY /* DM_KEY = INTEGER NOT NULL */,
CLIENTACTION_ID DM_KEY /* DM_KEY = INTEGER NOT NULL */,
SUBACTION_ID DM_INTEGER /* DM_INTEGER = INTEGER */,
IP_ADDRESS DM_VARCHAR20 /* DM_VARCHAR20 = VARCHAR(20) */,
DETAILS DM_MEMO /* DM_MEMO = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */
);


ALTER TABLE CLIENTACTIONLOG ADD CONSTRAINT PK_CLIENTACTIONLOG PRIMARY KEY (CLIENTACTIONLOG_ID);

ALTER TABLE CLIENTACTIONLOG ADD CONSTRAINT FK_CLIENTACTIONLOG_1 FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTINFO (CLIENT_ID) ON DELETE CASCADE;

ALTER TABLE CLIENTACTIONLOG ADD CONSTRAINT FK_CLIENTACTIONLOG_2 FOREIGN KEY (CLIENTACTION_ID) REFERENCES CLIENTACTIONLIST (CLIENTACTION_ID) ON DELETE SET NULL;


THe IB_Monitor results are below:

*************************
ClientInfo insert
*************************

/*---
PREPARE STATEMENT
TR_HANDLE = 3579008
STMT_HANDLE = 3579464
Insert into ClientInfo(Client_ID, Email, UserPassword, Admin_Rights)
values (? /* Client_ID */ , ? /* Email */ , ? /* UserPassword */ , 'F')
FIELDS = [ Version 1 SQLd 0 SQLn 30 ]
----*/

/*---
PREPARE STATEMENT
TR_HANDLE = 3579368
STMT_HANDLE = 3578792
SELECT GEN_ID ( GEN_CLIENT_ID, 1 )
FROM RDB$DATABASE
PLAN (RDB$DATABASE NATURAL)
FIELDS = [ Version 1 SQLd 1 SQLn 1
GEN_ID = 4294967296 ]
----*/

/*---
EXECUTE2 DSQL
TR_HANDLE = 3579368
STMT_HANDLE = 3578792
PARAMS = [ ]
FIELDS = [ Version 1 SQLd 1 SQLn 1
GEN_ID = 18 ]
SELECT COUNT: 1
----*/

/*---
EXECUTE STATEMENT
TR_HANDLE = 3579008
STMT_HANDLE = 3579464
PARAMS = [ Version 1 SQLd 3 SQLn 3
[CLIENT_ID] = 18
[EMAIL] = 'none@...'
[USERPASSWORD] = 'test' ]
INSERT COUNT: 1
----*/

*************************
ClientActionLog insert
*************************

/*---
PREPARE STATEMENT
TR_HANDLE = 3579008
STMT_HANDLE = 3578792
Insert into ClientActionLog
(Client_ID, ClientAction_ID, SubAction_ID, IP_Address, Details)
values (? /* Client_ID */ , ? /* ClientActoin_ID */ , ? /* SubAction_ID */ , ? /* IP_Address */ , ? /* Details */ )
FIELDS = [ Version 1 SQLd 0 SQLn 30 ]
----*/

/*---
EXECUTE STATEMENT
TR_HANDLE = 3579008
STMT_HANDLE = 3578792
PARAMS = [ Version 1 SQLd 5 SQLn 5
[CLIENT_ID] = 18
[CLIENTACTOIN_ID] = 5
[SUBACTION_ID] = <NULL>
[IP_ADDRESS] = ''
[DETAILS] = BLOB ID ( 0, 1 ) ]
ERRCODE = 335544466
----*/

/*---
INTERPRET BUFFER =
ERRCODE = 85
----*/

/*---
INTERPRET BUFFER = violation of FOREIGN KEY constraint "FK_CLIENTACTIONLOG_1" on table "CLIENTACTIONLOG"
ERRCODE = -1
----*/