Foreign Keys won’t update

[k2a]

I have a view with 3 joined tables. The parent and child tables are interconnected by means of foreign keys . Stored procedures _ri_update have been created. What I expect after adding data into the view and issuing an insert command followed by a tableupdate command that all fields in the tables including the foreign keys are all updated.

However, all fields are updated in tables including the primary keys but all foreign keys remain empty. My workaround to this problem is grapping the primary keys of the updated parent tables and place them into the child table. This is not a solution I am happy with.

Anyone got any ideas about how I can solve it?
Any suggestions are appreciated.
Klaus

 

[Mike Lewis]

Stored procedures _ri_update have been created.

Klaus, did you create the stored procedures with the RI Wizard? If so, are you sure you set the update type to "cascade"?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

So you have a parent-child relationship in a view and you want a single INSERT to update both the parent and all the children?

That's expecting an awful lot of smarts in the database. What should it do if it commits the change for the parent but one of the child update fails? What should it do if it commits the child updates first and then the parent fails?

Multi-table views take special handling.

Looking for help from an update trigger when you're doing an insert is barking up the wrong tree entirely.

 

[Olaf Doschke]

The cascading update of RI waizard stored procs is not a solution, as it only would cover cascading the update of primary keys to foreign keys of child records, which already exist in the database. If you save parent and child records, the parant is saved first and no child records are in the database to cascade that update to, they'r ejust in the view buffer, but stored procs don't work on views.

The situation is even worse: Views don't inherit default values or relations or indexes or whatever.

So about your main complaint: Show me any database doing this straight as you think it should be automatic, in updatable views.

The solution is to not depend on the databse to generate primary keys and use uniqueidentifier, also called GUID or UUID. Then you can create new view record, but still will need to cascade the primary keys you generate in parent view records to child view records yourself.

The other solution is to save parent data right away and so already have the primary key from the databse to put into new child records. That will remove the chance to tablerevert() all changes including the new parent records, but losing work is harder than a few too much records in the data.

Overall, the solution is not within short distance in your situation, Don't try to fiddle around and try to make it work the way you wish and think it should be. Relax and take a different approach.
The one thing working perfect for me is UUIDs and this is code genereating it:

* Test output
_Screen.FontName = "Courier"
? FullGUID()
? uniqueidentifier()
? BinaryGUID()

Procedure BinaryGUID()
   * create a binary Char(16) GUID
   Local lcBuffer
   
   lcBuffer = Space(16)+Chr(0)
   =CoCreateGuid(@lcBuffer)
   Return Left(lcBuffer,16)
Endproc

Procedure FullGUID()
   * create GUID in the format for a Char(38) field
   * {4DDF8EA2-30F5-4016-9554-5B9C16FA2D91}
   Local lcBuffer
   
   lcBuffer = Space(128)
   =StringFromGUID2( BinaryGUID(), @lcBuffer, Len(lcBuffer) )
   
   Return Left(Chrtran(lcBuffer,Chr(0),''),38)
Endproc

Procedure UniqueIdentifier()
   * create GUID in the format, which SQL Server displays and returns, needs Char(36)
   * 4DDF8EA2-30F5-4016-9554-5B9C16FA2D91
   Return Chrtran(FullGUID(),'{}','')
Endproc

Procedure CoCreateGuid(tcBuffer)
   Declare Integer CoCreateGuid In ole32;
      STRING @ pguid

   Return CoCreateGuid(@tcBuffer)
Endproc

Procedure StringFromGUID2(tcGUID, tcBuffer, tnMaxlen)
   Declare Integer StringFromGUID2 In ole32;
      STRING    rguid,;
      STRING  @ lpsz,;
      INTEGER   cchMax   

   Return StringFromGUID2(tcGUID, @tcBuffer, tnMaxlen)
Endproc

Put these as stored procs and you can set a GUID field default value to uniqueidentifier. This is compatible to MSSQL Servers newid() als generating a uniqueidentifier type value. Add a primary index to the guid field and it's your primary key and you can generate new ids both in the database but also at the client side or even offline (eg at a disconnected notebook while travelling) and merge data into a central database without overlaps of value ranges, like with autoinc integers.

Bye, Olaf.

 

[Olaf Doschke]

Forgot: In any case, even with UUIDs, it's your responsibility to forward them as foreign key to child data. What databases offer as automatic foreign key actions is to check a foreign key contraint and reject child records with an invalid key value not corresponding to a parent record. And the other thing is cascading of primary key changes. There is no such thing as inheritance of foreign keys in relational databases. That may be done on the level of OOP, entity framework or other object relational mappers. VFP doesn't offer that, you have to roll your own, if you want to go in that direction. Views could be part of that, but obviously are not OOP.

And in respect of updating primary keys I am one of those, who think recreating primary keys should be forbidden. In the form of RI (referential integrity) rules, that means restricting update of primary keys, if there are child records. And I'd say even not, if there is only the parent record alone. That's not just because bad performance of cascades. The only intent of keys is to reidentify a record, and that means you create an id that's forever. Even though they should not get displayed, they get printed and you never can update paper, also they get around in data exports etc. where it's okay, as they are also needed there to make relations. So even just for that matter a primary key should be generated once and for all.

Bye, Olaf.

 

[Dan Freeman]

I somehow forgot to paste in what I meant to paste in above :-( so I'll try again.

I agree with Olaf that primary keys are inviolate. I'm less of a GUID zealot but they're usable too.

The mechanics are coming from a space in my brain that has some cobwebs but it goes something like this:
[ul]
[li]Manually start a transaction[/li]
[li]Alter the view's UpdateTables and UpdateFields properties to only reflect the parent table[/li]
[li]TableUpdate(), trapping errors (if any) and grabbing the parent's new ID[/li]
[li]Alter the view's UpdateTables and UpdateFields properties to only reflect the child table, with the newly created ID now as FK[/li]
[li]TableUpdate(), trapping errors (if any)[/li]
[li]Repeat for each child table[/li]
[li]Manually commit (or rollback) the transaction[/li]
[/ul]
This process sounds a lot more difficult than it is. It's just fiddly, and not particularly maintenance-friendly. But it's one reason few people use multi-table updateable views.

 

[Olaf Doschke]

I agree with dan. This is what I also use in a framework mainly using Cursoradapters. You can predefine cascading tableupdates by chaining busienss objects there, it's working the same way, though I don't have the need to cascade new ids to child data, I already did that at creation.

I can understand, if people still tend to think of 36 bytes as waste of space, even if a record can be much larger. ints are also easier to remember and set and use in debugging. But if you go for int, go for a stored proc creating them. You can make use of autoinc by letting that stored proc use a system table with an autoinc field to hand out the next int. Either per table of the database or for the database as a whole, doesn't matter. But autoinc ints also hinder appending data and easy use of views. With a stored proc that already increments a counter while you create and not yet save data, you also have no problem with precreating the final IDs as primary or foreign keys. And that also eliminates the cascading problem.

Bye, Olaf.

 

[k2a]

Thanks for all the replies guys. Please excuse my ignorance relating to views. I’m deeply impressed about the quick response to my question and that even on the weekend.

First of all I’ve to try to understand your comments and suggestions but I must admit that most of it goes over my head.

Would it be even better and simpler in my case to surrender the view approach and add new data to the tables directly? But I would expect that vfp database doing the foreign keys updates and not me.

Thanks again to all.
Klaus

 

[Olaf Doschke]

In regard to the database doing foreign key updates, think about the following situation:

You add 2 parent records to the view with each 1 or more child records. How should the database know, which child records should get what primary key as their foreign key? Foreign key assignment is not a database job. It's you, who decides which child records belong to which parent records.

Working on the tables to get primary keys in the first place was one solution I suggested. It's not ideal of course, if you intend to work with views. But you are able to combine it, you'd add new records and your view or views would then just be used to read, display and update or delete records, ou'd only change your insert strategy to insert into dbf.

If you already do assign prliminary keys like -1,-2 or even 1,2,3 as primary and foreign keys, you still expect too much of the databnase to make automatic updates. The database has no facility to temmporarily remember which prliminary primary keys got which real primary key values and which foreign keys therefore would need to be changed to what value. The database simply ignores what you put into id fields and generates it's id, but there is no foreign key generation.

But if you already give your records preliminary keys the change to give them real keys is the easiest solution. I assumed you use autoinc, that might not be the case, but whatever you use, should be moved to a stored procedure, which can provide keys. That can be called as default value of the primary key id fields (not for foreign keys, there is no chance to know that automatically) and you're also able to call that stored proc to put in primary key values and use them as foreign keys, so the database has no need to generate them at the moment you save view data, as final key values are already in the view(s).

Bye, Olaf.

 

[k2a]

Hi Olaf
Thank you for all your affords you made so far helping me to find a better approach updating the foreign keys.

If you tell me as a vfp expert that foreign key assignment is not a database job. Then I ask you for what reason are the relationship assignments made in the Database Designer between the primary keys to the foreign keys necessary? And for what reason are the stored procedures _ri_update_tablenames created for?

In my case I use only line buffering so to add data to my tables are done record for record. When issue an insert followed by a tableupdate command the data and primary keys are updated right away. Because the foreign keys remain empty so I grab the primary keys and place them into the appropriate foreign keys.

So if a view cannot handle foreign key updates, as I wrongly thought it is possible, then I have to accept that fact. If there is no better way to update my foreign keys, then I can leave it as it is. At least my app works with it.

Klaus

 

[Olaf Doschke]

The _ri_update_tablename procedures is providing the feature of checking foreign key assignments you made with parent table primary keys. No more, no less. There always are reccount("parenttable") possible values for a foreign key, and they are checked by opening parent table and seeking the foreign key of updated records with parent tables.

Besides that ri code can cascade primary key updates, but only to already exisitng child data at that moment, not to buffered or view data.

Even if you only add one record per record buffering mode, the database cannot make the assumption the child record must belong to the new parent record, you could also add a new parent and a child to an already existing other parent.

The main use for the referential rules is to check, whether you provide a correct foreign key, which exists as primary key in the parent table. No more, no less.

Are we really talking about a 1:1 relationship, perhaps? That would make me understand why you have your impression of how things should be handled, but even then I fear, there is no way specifying the foreign key as getting it's value from the parent key field, even though that's how you would define a 1:1 relationship, besides that this foreign key then also has to be unique by a candidate or primary index.

Bye, Olaf.