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.
Last Modified: 02-OCT-00