I have 2 tables
Table1 fields (Id, Name)
Table2 fields (Table1Id, OtherData) Table1Id defined as Foreign Key to Table1.

I set the master details relation on the table2 properties on MasterSource, MasterFields, IndexName.

I try to update both tables and run

Table1.Post;
Before Table2.Post the  Table2. State change to dsBrowse and it raises the error dataset, not in Edit or Insert mode.

What I missed?

Hershcu

Wrong sequence - change and post the detail table first then the master. If you change the master and post the relationship will try and move the pointer to the detail table so it will do an automatic post, move to the appropriate detail record(s).

Think of it as though you change the master record then change a filter on the detail table.

Roy Lambert

Thanks, Roy

I try it but in case I insert 2 new records one master and one detail
I get the error "Unknown ForeignKey on the master table"

Hershcu


Can you post the code you're using please.

Roy Lambert

Thanks, Roy

The code:

tbAnmlDangerAnimalsId.AsInteger := tbAnimalsId.AsInteger;
tbAnmlDanger.Post;
tbAnimals.Post;

tbAnimals is the master table.
tbAnmlDanger the details table.
AnimalId field is the foreignkey on details table

If tbAnimals is in Edit mode it works but it both tables are in Insert mode it raise the error:
#1004 The foreignkey constraint AnimalId for the table tbAnmlDanger has been violated...

Sorin

Hershcu


I'll start by declaring that I hate database enforced referential integrity and refuse to use it, as far as practicable, myself. I know it can be useful / essential when multiple applications can access & modify a database but otherwise its given me to much pain over the years.

I think I can see what's happening its the difference between edit & insert mode, combined with the way master-detail means the tables cursors are moved.

When editing a master-detail the master record has an ID however it was generated so

tbAnmlDangerAnimalsId.AsInteger := tbAnimalsId.AsInteger;

is fine - you're posting a valid ID to the detail record so that when you post it it has a valid foreign key in place.

When you're in insert mode the ID for the master table does not exist until after its posted, in fact the entire record does not exist until posted. Either the ID is automatically generated or manually generated but as far as the table is concerned it doesn't exist. Try it -


showmessage(tbAnimalsId.AsString);
tbAnmlDangerAnimalsId.AsInteger := tbAnimalsId.AsInteger;
tbAnmlDanger.Post;
tbAnimals.Post;


in insert mode if its an autoinc you'll see a blank, in edit mode you'll see the value.

You need different logic for insert & edit without a master ID change and a third logic for edit with a master ID change.

Alternatively - dump the master-detail relationship and manage it yourself. That's what I do - its a little bit of code needed to apply a filter to the detail relationship - essentially what the master-detail does but under your control. You'll need to post tbAnimals first but the detail cursor won't move so you can then do the rest to the detail table.

Roy Lambert

Thank, Roy

Indeed it clarifies the issue a lot

Sorin