Subject Re: [IBO] Foregn Key Problem
Author Helen Borrie
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.

I don't know what exception you expect to catch by putting the
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

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.