Hi Grant,

Forget the SP for the moment.

Open up database work bench and try and manually insert a record into
BUS_KEY_FEAT. We really don't have enough information to trouble
shoot for you, and it is difficult to grasp what you are trying
without seeing some data in there.

We have about 200 foreign key definitions in our product, and they
all behave as expected. That is not to say we have never pulled our
hair out, here are some gotchas to remember.

What are your triggers doing to your FK's?
Is the foreign key referring to the tables and fields that you think
it is? (dont answer yes, check it again)
Is there any other transaction that may have deleted the parent
record that you can not see yet?

Adam




--- In firebird-support@yahoogroups.com, Grant Brown <grant@s...>
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
>
> ------------------ 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@s...
> Web : www.sitedoc.com.au
>
> SiteDoc - Easy to Use - Powerful Results