Handling "General SQL error: violation of FOREIGN KEY constraint . . ." error

By: Borland Staff

Abstract: The VCL posts detail records before master records

Problem:
When using a master-detail relationship the error "General SQL error: violation of FOREIGN KEY 
constraint . . ." error messages appear when I try to simultaneously post records in the master 
and detail table.


Solution:
The VCL code posts the records to the detail table first, then posts the records to the master table.
This is done for local tables (dBASE, Paradox, and ASCII), ODBC data sources, InterBase, Oracle,
and other supported RDBMS servers.  In posting the details records first, since the master record
is not yet in the table, an error is generated on the server and passed to the BDE.   In case of local 
tables the error is generated in the BDE.

One easy way of overcoming the exception is to do the following:

- In the BeforeEdit event of the detail TTable or TQuery object place the following lines of code:

  // If using TQuery object replace "TTable" with "TQuery"

  if ((DataSet as TTable).MasterSource.State = dsInsert) then
    (TTable(DataSet).MasterSource as TTable).Post;

What this will do is that when record data is typed in on the detail table, the detail table will 
switch its state into dsInsert mode.  The BeforeInsert event will fire off the above code that will
place the new master record in the master table.  If the new record in the detail table is aborted 
and the new record remains in the master table.  What will happen in the application is that the 
new master is still added first (no change here), the only difference is that the new master and 
detail records are not added at the click of a "Post" button or other such triggering UI component.



Two other alternatives that are more difficult to implement are suggested below:

- Remove the foreign key constraint and require that the client applications check for 
   the key in the master table.

- Do not use the MasterSource and MasterFields properties in the TTable 
  and TQuery objects and manage the master-detail relationship in the 
  application code.

Server Response from: ETNASC03