Change key value on Master detail

[lauchuen]

Code: Select all

master table (
id varchar(50) primary key,
name varchar(50)
);

detail table (
id varchar(50) primary key,
master_id varchar(50),
name varchar(50)
);

hi,

above tables are master-detail relationship, and cachedupdate is true, separate two grid link with them in the same form.
firstly i insert a record on master table id = 'A', then go to detail table insert 2 records, the master_id append 'A' by default due to the master-detail relationship, enter some value for 2 records like id = 'A1' & 'A2'
now the problem is when i update the master table id from 'A' to 'B', all details records disappear. how to implement a cascade update for detail.master_id ?

thx & regards

jacky

[DemetrionQ]

Hello.

To solve the problem, you can add an external key with cascade updating for the master_id field, for example:

Code: Select all

ALTER TABLE detail_table ADD CONSTRAINT fk_master_id FOREIGN KEY (master_id) REFERENCES master_table (id) ON UPDATE CASCADE;

As a result, updating detail_table.master_id will be automatically performed on the server-side when modifying master_table.id

[lauchuen]

i know the foreign key has support cascade update function. but the problem is now i'm using the cachedupdated mode.

refer to your solution, is that means pgdac doesn't support this features or it occur by bugs?

[DemetrionQ]

Hello.

    The fact that records in the detail query are not updated on modifying the master query is standard behaviour for master-detail relationship. PgDAC reproduces this behaviour. If you know components, that automatically update the detail query when modifying the master query, please name them.

[lauchuen]

oh, sorry, my bad, maybe my question make u confused.

how to implement a cascade update for detail.master_id ?

it doesn't mean that the dataset need to direct update the database value, i'm talking about the UI level - the grid view.

ok, maybe we start again, following the new table structure:

Code: Select all

master table (
id varchar(50) primary key,
name varchar(50)
);

detail table (
id varchar(50) primary key,
master_id varchar(50),
name varchar(50),
FOREIGN KEY (master_id) REFERENCES master (id) ON DELETE RESTRICT ON UPDATE CASCADE,
);

two PgQuery dataset, master-detail relationship, cachedupdate mode is True, and two dbgrid are connected with them separately. and then:
1. insert a record on master grid, like id = 'A'
2. insert two record on detail grid, like id = 'A1', master_id = 'A' & id = 'A2', master_id = 'A'
3. update master grid id from 'A' to 'B'
4. ** important ** 'A1' & 'A2' records in detail grid are disappeared

so, my problem is how to maintain detail grid keep displaying 'A1' & 'A2' records when master key changed?

thanks!

[DemetrionQ]

Hello.

    If CachedUpdate is True, there is no solution for master-detail relationship.
    If CachedUpdate is False, then you can call detail_query.Refresh after master_query.Post (simply call detail_query.Refresh in the master_query.AfterPost event handler). This will update detail_query by loading data from the table, that was updated by the foreign key.

    If you need update caching, there is no need to use CachedUpdate. You can use a transaction. All updates made in the context of the transaction will be cached at the server until you perform Commit or Rollback.