How to Programmatically Populate Duplicate Detail Key Fields in Delphi FireDac

Question

I am trying to set up a relational database in Delphi FireDac. I need the detail key field to match the master key. This will require duplicate data in the detail key field.

I attempted to populate the field in the AfterInsert event of the TFDTable, but got the error

"field 'id' cannot be modified"

I also tried setting the AutoGenerateValue of the TFDTable to arDefault, but have not been able to update the record. I get the error

null value in column "id" of relation "line" violates not-null constraint

The field is defined as not null but not unique or primary key.

How can I generate the same value in multiple detail key fields as the master primary key?

Answer 1

If you post it as an answer I will accept

Use the OnNewRecordEvent of the Detail table. Ensure detail key field is not read-only, especially if you declared fields via EDI contextual menu.

Let us say you have a "master" table Artists

CREATE TABLE artists (
ArtistId INTEGER        PRIMARY KEY AUTOINCREMENT
                        NOT NULL,
Name     NVARCHAR (120));

And detail table Albums

CREATE TABLE albums (
AlbumId  INTEGER        PRIMARY KEY AUTOINCREMENT
                        NOT NULL,
Title    NVARCHAR (160) NOT NULL,
ArtistId INTEGER        NOT NULL,
FOREIGN KEY (
    ArtistId
)
REFERENCES artists (ArtistId) ON DELETE NO ACTION
                              ON UPDATE NO ACTION);

then ArtistId have to be read/write