How to assign foreign key in a master detail relationship using generator in Delphi XE2?

Question

As an example:

I have two tables in firebird:

TB_CUSTOMER

• IDCUSTOMER (autoincrement generator)
• CUSTOMERNAME

TB_PHONE

• IDPHONE
• IDCUSTOMER (foreing key from TB_CUSTOMER)
• PHONE

I have a registration form developed in Delphi. The table data TB_PHONE are handled using a dbgrid. I can not assign the value of the field IDCUSTOMER in TB_PHONE, because it was not generated by the Firebird generator. How can I make the relationship between the tables? I want to implement it without first saving the table data TB_CUSTOMER. I'm using datamodules with IBDAC.

Any sugest?

Answer 1

Before detail table can be inserted into, you should have PK-index over master-table updated and having proper master-ID in it. That means that some piece of code should insert master-record before inserting detail-record. Where this piece of code would be - is only limited by your fantasy. Few arrangements include

1. insert the master-row in your application. Read the id of the row. Insert detail-row using this id.

2. read ID from then Generator, then insert both rows (master 1st) using the obtained ID

3. create a stored procedure, inserting both rows and returning ID (implementing #1 or #2 server-side)

4. use EXECUTE BLOCK - basically ad hoc anonymous SQL procedure. But that only is available in FB 2.x and except for not using namespace it is inferior to #3.

5. add BEFORE INSERT trigger onto detail table, searching for ID in master and adding one if not found. This would slow down all insert operations (even when master-ID already exists - that should be checked), would not be able to fill all other master columns but ID and is potentially dangerous due to hiding application logic problems. But still that can be implemented (though ugly and dirty method)

6. create master-join-detail VIEW and add INSERT trigger for it, propagating the new view-row into both master-table and details-table.

et cetera

Answer 2

I want to implement it without first saving the table data TB_CUSTOMER

There's your problem. You need the primary key from the master table before you can save the detail. That's just the way it works. But if what you want is to make sure that the values get saved together, you can do that as a transaction. In Firebird, you can do it like this:

• Begin a transaction. Exactly how you do that depends on which DB library you're using to access your Firebird database.
• Run an INSERT INTO ... RETURNING statement to insert the row into your master table and retrieve the generated value as a single operation.
• Use the generated PK value to fill in the FK value on your detail table.
• Insert the detail row.
• Commit the transaction.