Don't remove the Foriegn Key, just add a Primary Key to the child table. Your structures will look like this:
Parent table :-
Primary Key
Master Value
Child Table :-
Primary Key
Foreign Key (Parent's Primary Key)
Child Value
You can still load sets of records based on the parent's key by creating your SQL syntax as
SELECT * from ChildTable WHERE ChildTable.ForeignKey=?ParentKey
But for updating, set the Key field to be the Primary Key of the Child Table. This will guarantee that the resulting SQL from TABLEUPDATE() will be constructed properly.
What is actually going on here is that the View mechanism is creating SQL based on the definitions you give for the view. When you incorrectly had the Key as the Foreign Key, you were ending up with the following SQL on TABLEUPDATE() [for example]:
DELETE FROM ChildTable WHERE ForeignKey=1
Which of course will delete all rows in ChildTable where ForeignKey is 1. To treat each record individually, you want the generated SQL to be:
DELETE FROM ChildTable WHERE PrimaryKey=1
You might want to use an auto-incrementing primary key here. If you need help with one let me know.
>>>>>I'm facing a very strange problem while deleting records in view...
>>>>>
>>>>>When I click on the child view grid to delete one rec. and save it...
>>>>>
>>>>>All the records are getting automatically deleted (for that one parent rec..)
>>>>>
>>>>>Anybody having any suggestions to solve this..
>>>>
>>>>How do you delete the record? Using any condition or simple DELETE? Is there any RI involved on DELETE?
>>>
>>>I just click on the delete mark, provided in grid to delete record..
>>
>>Check your key field. Your key field must be a unique identifier (primary key) form an updatable view to work correctly. For example,
>>
>>Structure of View
>>
>>nKey I
>>nFKey I
>>
>>Data:
>>
>>nKey nFKey
>>
>>1 1
>>2 1
>>3 1
>>4 2
>>5 2
>>6 3
>>
>>nKey is the primary (unique) key. If I set up my view with nKey as the key field, delete and TABLEUPDATE() then only the records deleted are deleted, as expected. But if I set up my key field as nFKey (which I am not prevented from doing), then deleting record one and TABLEUPDATE() will actually delete records 1-3.
>>
>>Hope this helps.
>
>
>Thanks !! U r right, problem is this only...
>
>I'm having one parent table and 2-3 child tables, file structure is :-
>
>Parent table :-
>Primary Key
>Master Value
>
>Child Table :-
>Foreign Key (Parent's Primary Key)
>Child Value
>
>I'm not having primary key field in child tables, only foreign key which relates it to parent...
>
>Pls advs. how I can solve this.. as if I remove key field, none of the fields are updatable...
Eric Shaneson
Cutting Edge Consulting