Turn Off Referential Integrity

[Michael Humby]

Hi All,

Anyone know how to turn off/on 'Referential Integrity' using xbasic?

Recently had a nasty experience with Auto Increment field. The field supplies the unique link in the parent table for several child tables.

One of my Runtime users somehow managed to create a completely blank record in the parent with no auto increment value and from then on the auto increment restarted from zero creating duplicated values in the parent linking fields. The child tables contain sensitive tables for payments etcetera and became double linked to the wrong child tables and is not apparent to the user until more damage has occurred.

My users are forced to enter new records by clicking on a New Record button with the field rule Cansave event on Lname"""".

My user admits to having problems with their computer crashing (Not only when using my Runtime App) to the extent they now use their laptop so I am hoping this is a one off problem.

Fortunately, I was able to obtain a recent backup zip and make the repair on my computer.

To repair the parent involved drastic surgery, because even by removing the blank record and the duplicated parent records still forced the increment to duplicate. The answer was to turn off referential integrity then remove both the blank record and the duplicated records and then duplicate the parent table, 'records only' to a temp.dbf, zap the existing parent then append back in again into the original parent with the auto increment still set as on. Finally reset referential integrity.

I would like to swap experiences to see how to avoid this blank record scenario occurring again.

Anyone know how to turn off/on 'Referential Integrity' using xbasic? If so, any future mishaps could be repaired remotely with a patch script.

e.g.
Remove Integrity (Otherwise I loose all existing child records linked to original parent)
Delete duplicates
Duplicate to temp.dbf
Zap parent table
Append back from temp
Re-Set Integrity

Would appreciate any feed back on this subject.

Michael

[Tom Cone Jr]

RE: Turn Off Referential Integrity

Michael,

Since no one else has responded I'll offer my 2 pence.

Turn off referential integrity throughout the application. Enforce the equivalent behavior yourself using scripts tied to buttons or events with change or delete primary table records.

-- tom

[Michael Humby]

RE: Turn Off Referential Integrity

Tom,

I had completly overlooked the the fact that if I delete duplicates records from outside the set the the cascade changes doe not take effect.

Would still like to hear of other experiences of how a blank record can forced into a table.

Michael

[Tom Cone Jr]

RE: Turn Off Referential Integrity

Michael,

I don't have an answer that might explain how the blank record was entered. However, you can cancel() a save if a field in the current record is empty or zero using the CanSaveRecord event in the table field rules. You might want to establish this in field rules for the primary table.

-- tom

[Michael Humby]

RE: Turn Off Referential Integrity

Hi Tom,

I have cansave traps at the form level, the only place that the user can enter new records, but a blank record was forced somehow.

I have now placed in the record's cansave event in field rules.
t=table.current()
If t.idnum.is_blank() then
cancel()
End If

I will now make this a firm policy for all future tables that use the Auto Increment field rule. Hopefully this should guarantee no blank records will occur.

Michael

[Al Buchholz]

RE: Turn Off Referential Integrity



Michael Humby wrote:
-------------------------------
My user admits to having problems with their computer crashing (Not only when using my Runtime App) to the extent they now use their laptop so I am hoping this is a one off problem.

If the table's legs are unstable, it's impossible to make the table top steady.....

Sounds like it's a good thing that you are not using that machine anymore.