where's the referential integrity?

[hal9000]

I have a simple 2 table parent child relationship. I store the parent primary key value (a unique ID) in a foreign key field in the child table.

No matter what I do, FM continues to allow the deletion of parent records with dependent children. I don't think that should be possible in a relational database. How do you maintain referential integrity like that?

I'm a newbie, so I may be missing something.

thanks in advance!

Hal

[LaRetta]

Hi Hal, welcome to FM Forums!

No matter what I do, FM continues to allow the deletion of parent records with dependent children.

As always, it is the Developer's responsibility to fully control functionality of a design. Some tables would warrant also deleting children (deleting an invoice should delete the lineitems as well) and some parent deletions would NOT warrant deleting children (two parent records sharing the SAME address shouldn't delete the address). Each parent/child relationship needs to be evaluated independently and, according to the business rules outlined, the developer then enforces those rules. How? All deletions should be 1) restricted using privileges when appropriate, 2) scripted and 3) you might use 'delete children when deleting parent' in the relation graph when appropriate.

You can restrict all Users from deleting from the FM Menu via privileges and then create a script which, table by table, asks and responds according to the rules and whether you graph allows cascading deletes (and whether your script is set to run with full privileges).

LaRetta :wink2:

[Søren Dyhr]

If orphans are the problem are there measures in the relations def. as shown above!

--sd

[hal9000]

Thanks for the advice. All I want to do is prevent a parent that has children from being deleted. You mention that this would have to be scripted. Can you point me in the direction of an example script that would prevent a parent with children from being deleted?

[LaRetta]

If [ not IsEmpty ( childTable::fk_parentID ) ]

Show Custom Dialog [ OK ; "This parent has kids. You can't delete it!" ]

Else

Delete Record/Request[]

End If

:wink2:

Edited February 14, 2009 by Guest
Added parenth

[David Jondreau]

It doesn't have to be scripted. You can place this limitation in the access privileges for the table. A script in addition to the privileges is nice though because you can create a more polite interface.

[LaRetta]

Notice above, DJ, that I also said it could be controlled via privileges, that scripting was only one way. A small thing, but I didn't want you to think I didn't know about privilege restrictions on deletions. :wink2:

[David Jondreau]

Don't you know that I know that you know? But Hal9000 seemed to not know. Ya know?

[hal9000]

Thank you so much for the code LaRetta, that worked like a charm.

The privilege setting approach mentioned by D J would not work for me in this case because I would not want *any* user to be able to delete parents with children, even admin.

[LaRetta]

Don't you know that I know that you know? But Hal9000 seemed to not know. Ya know?

Usually if someone knows that someone who posted previously knew, then they would say, "As so-and-so already mentioned..." so I assumed you didn't know that I knew or that I had already said so, or you would have said you knew I knew. You know?

I said it was a small thing which you didn't seem to know either. :wink2:

[comment]

The privilege setting approach mentioned by D J would not work for me in this case because I would not want *any* user to be able to delete parents with children, even admin.

That is not possible under any circumstances. Users with full-access privileges can do anything they want - same as you - and can override any restrictions you have placed.

As LaRetta wrote in her first post, you must FIRST take away user's privilege to delete records AND THEN provide them with a scripted alternative. If you have restricted users from deleting records altogether, then the script is the only way for them to override the restriction. If your restriction is conditional, then the script is merely a cosmetic measure (as DJ wrote). But in any case, a script by itself cannot provide any real security measures.

[mr_vodka]

Usually if someone knows that someone who posted previously knew, then they would say, "As so-and-so already mentioned..." so I assumed you didn't know that I knew or that I had already said so, or you would have said you knew I knew. You know?

I said it was a small thing which you didn't seem to know either.

I hope you guys know that you guys gave me a migraine. You know? :

[hal9000]

OK, I get it (I hope!)

I will be taking away user's privileges to delete records and will rely on the script for deleting records.