At 02:13 PM 29/04/2005 +1000, you wrote:

>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

Cool down, Grant. :-))

See inline comment and following comments...

>------------------ 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; <------------------LOOKS SUSPICIOUS
>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,
>....

integer = 32-bit, precision 9

>--------- BUS_KEY_FEAT table -----------------------------------
>
>RECREATE TABLE BUS_KEY_FEAT
>(
> HEAD_ID BIGINT,

BigInt = 64-bit, precision 18

Now, while the DB engine itself will usually resolve type mismatches, I
can't guarantee that it works in the direction of (64-bit referencing a
32-bit key). That is, theoretically you can push a 32-bit integer into a
64-bit; but you can't push a 64-bit integer into a 32-bit.

Even if the db engine *does* do something intelligent with mismatched
integer types under these conditions, you are using Delphi, which
can't. As a result, a lot of Delphi interfaces handle BigInt (or
Numeric(18,0) in the case of IB) as strings. There's a chance that the FK
mismatch originates from the parameter being passed in the input variable HEAD.

As data access interfaces usually have good reasons for manipulating values
in these ways, it pays to keep your DB design nice and tidy to avoid such
messups. Though legacy data can often present pain and trouble, with which
we just have to do our best, it's rare for there to be a reasonable
mathematical argument supporting the idea of mismatching the types of
referential keys by design.

Recreate BUSDETAILS so that the two keys actually match for type, and see
whether that solves the problem.

Now -to the inline comment on the SUSPEND statement. Remove it. Your
procedure is (or ought to be) an executable procedure.

In fact, I can't see any logic to returning the value of the input variable
back to the client at all, since that's where it started from...

./heLen