Hi Helen,

I have repeated the query in question, and behavior is the same. The DDL
statements and the Insert query have been pasted into the text below. Hope
that is what you need.

tks,

jwc

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Friday, June 06, 2003 5:35 AM
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] Foregn Key Problem

At 11:13 PM 5/06/2003 -0400, you wrote:

>My FB database has a number of one-on-many relationships with

auto-numbering

>triggers for all primary keys and foreign-key constraints to parent tables,
>where needed. I am populating the tables from an Access MDB using SQL

select

>(from MDB) and insert (to FB) queries.
>
>I use Delphi 6 running under Win2k Pro SP2. The FB structures were created
>using IBWB v. 1.3.1. For the MDB selects I am using a tQuery hooked to a
>tDataSource and Access ODBC driver, For the FB Inserts I use a tIboQuery
>hooked to tDataSource and tIboDatabase. Use of a datapump is not possible
>because the table structure of the MDB differs from that of the FB. I am
>parsing some of the MDB data columns prior to inserting the data.
>
>All of the Insert queries tested so far, including four tables containing
>foreign-key constraints, are working, except one, which generates an error
>when I attempt to insert a second record.
>
>The error is: exception class EIBO_ISCError with message 'ISC ERROR
>CODEE:335544466. ISC ERROR MESSAGE: violation of FOREIGN KEY constraint
>"name" on table "name" statement: TIBOInternalDataset: "<TApplication>.form
>name.query name".
>
>There is no apparent difference between this FK relationship and the other
>four, which accept inserts error-free.
>
>I am trapping SQL errors with a try...except surrounding the tIboQuery open
>statement, but that does not catch the IBO error. I would like to

understand

>how to trap the foreign key error.
>
>Would also like to have an idea of how to diagnose the foreign key error

and

>fix it.

As always, without the SQL, you're inviting nothing but mad guesses. Show
us the DDL of the tables concerned and show us the parameters for the
failing constraint.


DDL for the referred table:

RECREATE TABLE CONTRIBUTORS
(
CONTRIBUTORNR INTEGER NOT NULL,
CONTRIBUTORTYPE CHAR( 20) CHARACTER SET
NONE COLLATE NONE,
DATEENROLLED DATE,
NAME CHAR( 50) CHARACTER SET
NONE COLLATE NONE,
NOTES VARCHAR( 10000) CHARACTER SET
NONE COLLATE NONE
);

DDL for the referring table:

RECREATE TABLE INKIND
(
INKINDNR INTEGER NOT NULL,
CONTRIBUTIONNR INTEGER,
DESCRIPTION CHAR( 100) CHARACTER SET
NONE COLLATE NONE
);

The foreign-key constraint:

ALTER TABLE INKIND ADD CONSTRAINT FK_INKIND_CONTRIBUTIONS
FOREIGN KEY (CONTRIBUTIONNR) REFERENCES CONTRIBUTIONS
(CONTRIBUTIONNR)
ON DELETE CASCADE
ON UPDATE NO ACTION;

The Insert query that is failing, taken from the debugger watch window:

Insert into InKind ( ContributionNr, Description ) values( 56, '420 pens' )

I stopped execution at the point where the above query would be the next
item processed. I then looked at the "Contributors" table and verified that
a record with primary key value of 56 does in fact exist.

This is a program to populate FB tables from a single MDB source table.
Record 1 of the source has an in -kind contribution, and that is correctly
posted to the INKIND table using the same call. When a second in-kind
contribution needs to be posted (from source record 56), I get the error.

I don't know what exception you expect to catch by putting the dataset.open
call inside a try..except block. It won't catch a referential constraint
error - that will be caught in the Post event of the Insert operation - a
different event involving a different statement. Since you have reported
the text of the FK violation error, you obviously caught the exception
somewhere.

As far as I can determine, the error is caught in the Delphi debugger, or
perhaps by some default behavior that comes with IBO. I have not written any
error-trapping code beyond the try-except wrapper mentioned earlier.

I would like to capture errors like this with behavior that I can control.
This is all destined for the Web when ready.


The obvious causes of a foreign key constraint violation are:

--- trying to delete a row in the REFERENCES table where dependent foreign
rows exist and no CASCADE is defined for ON DELETE.

--- trying to add a dependent row whose foreign key is either NULL or
doesn't exist in the REFERENCES column

Unless you are forcing the client to respect the foreign key relationship
(by using masterlinking) you can't assign dependents to a REFERENCES
primary key on a master row that isn't posted - because, when you come to
post the detail record, the server doesn't know about the unposted master
row on the client.

Still, I'm wandering into guesswork country. Let's see the metadata and
the failing set of data.

Helen





Yahoo! Groups Sponsor
ADVERTISEMENT
Click Here!
<http://rd.yahoo.com/M=244522.3313099.4604523.1261774/D=egroupweb/S=17050071
83:HM/A=1595053/R=0/SIG=124orar12/*http://ashnin.com/clk/muryutaitakenattogy
o?YH=3313099&yhad=1595053>


___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .


[Non-text portions of this message have been removed]