Hi all,

Using Delphi 5, FIB+ and Database Work Bench, FB 1.5

I still am having problems inserting data into a table with Foreign Key
assigned.

The actual error message is (word for word)

DtM.SaveBusFeat: violation of foreign key constraint "".violation of
Foreign Key constraint "FK_BUS_FEAT" on table "BUS_KEY_FEAT".

To try and resolve this I have tried to insert a known value but still
get the error message and the insert fails.

for example

values (1,:tmpid,:DT,:ITEM_NO);

instead of

values (:HEAD,:tmpid,:DT,:ITEM_NO);

Could someone please help as this is driving me nuts

------------------ the stored procedure that inserts the value
-------------------------------
SET TERM ^^ ;
CREATE PROCEDURE P_SAVE_KEY_FEAT (
HEAD BigInt,
ITEM_NO BigInt,
DT BigInt)
returns (
RESULT_ID BigInt)
AS
declare variable tmpid BigInt=0;
begin
tmpid = GEN_ID(GEN_PK_KEY_FEAT_ID, 1);
insert into BUS_KEY_FEAT(HEAD_ID,DETECT_ID,DATATYPE,K_ITEM_ID)
values (:HEAD,:tmpid,:DT,:ITEM_NO);
RESULT_ID=:HEAD;
suspend;
end
^^
SET TERM ; ^^

---------- FK for BUS_KEY_FEAT ----------------------------

ALTER TABLE BUS_KEY_FEAT ADD CONSTRAINT FK_BUS_KEY_FEAT
FOREIGN KEY (HEAD_ID) REFERENCES BUSDETAILS
(DETECTID)
ON DELETE CASCADE
ON UPDATE NO ACTION;

--------- BUSDETAIL table -----------------------------------

RECREATE TABLE BUSDETAILS
(
DETECTID INTEGER DEFAULT -1 NOT NULL,
STATUSID BIGINT DEFAULT -1,
DATATYPE BIGINT DEFAULT -1,
BUSINESSTYPE BIGINT DEFAULT -1,
NOTESID BIGINT DEFAULT -1,
INDUSTRY_ID BIGINT DEFAULT -1,
INDUSTRY_SEC_ID BIGINT DEFAULT -1,
HOURS_TRADE_ID BIGINT DEFAULT -1,
HOURS_TRADE_DE_ID BIGINT DEFAULT -1,
BUILD_ST_ID BIGINT DEFAULT -1,
PARK_ST_ID BIGINT DEFAULT -1,
BUS_NAME VARCHAR( 150) CHARACTER SET ASCII
COLLATE ASCII,
STREET_NAME VARCHAR( 150) CHARACTER SET ASCII
COLLATE ASCII,
ACN_NO VARCHAR( 80) CHARACTER SET ASCII
COLLATE ASCII,
CREATE_DATE TIMESTAMP DEFAULT 'NOW',
EDIT_DATE TIMESTAMP DEFAULT 'NOW',
ACTIVE_STATS SMALLINT DEFAULT 1,
PARK_MEMO_ID BIGINT DEFAULT -1,
GEN_MEMO_ID BIGINT DEFAULT -1,
TOWN_ID BIGINT DEFAULT -1,
OURREF VARCHAR( 80) CHARACTER SET ASCII
COLLATE ASCII,
CONSTRAINT BUSDETAILS_PK PRIMARY KEY (DETECTID)
);

--------- BUS_KEY_FEAT table -----------------------------------

RECREATE TABLE BUS_KEY_FEAT
(
HEAD_ID BIGINT,
DETECT_ID BIGINT,
DATATYPE BIGINT,
K_ITEM_ID BIGINT
);

--
Regards,
Grant Brown

Product Development Manager
Phone : 02 4229 1185
Mobile : 0412 926 995
Email : grant@...
Web : www.sitedoc.com.au

SiteDoc - Easy to Use - Powerful Results