Hi,

[...this is probably off-topic as it's related to database applications, not Lazarus specifically...]

How do you guys handle such a case in database applications. Say you have an TAddressType object/record. Now you have a lot of Contacts with address information stored in a database (and address book type application)

Now I want to delete a specific AddressType, but that AddressType is being used by some Contacts in there address information.

What's my choices? How do you handle such a case?

* What happens if you simply try and delete the AddressType?
* DB referential integrity might stop the delete, but gives a lovely error. If I don't have referential integrity enabled, I have ghost address types lying around.
* Do I have to create some search query to run through all tables looking for that AddressType?
* Must I maybe extend my AddressType table to include some 'Active' boolean field. If set to False, it simply hides that record from the GUI, but still available in the database?

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

Not if all foreign keys to the table have the cascade delete option specified, then it is automatic. This is the option I most of the time use.

So something like
Create table AdresType (
ID INT PRIMARY KEY,
CODE Varchar(5)
);

CREATE TABLE ADDRESSES(
ID INT,
Adress varchar(100),
AddressType Int
);

ALTER table ADDRESSES ADD CONSTRAINT R_ADDRESSES_AdresType FOREIGN KEY
(AddressType) REFERENCES AdresType(ID) ON DELETE CASCADE;

When you delete AdresType record with ID 1, all ADDRESSES records that have AddressType =1 will automatically be deleted.

* Do I have to create some search query to run through all tables looking for that AddressType?

That is the manual way. I use it if a foreign key is not an option.

* Must I maybe extend my AddressType table to include some 'Active' boolean field. If set to False, it simply hides that record from the GUI, but still available in the database?

Some people do this, but I recommend against it, as you end up with a lot of junk in your database if it is actively used.

Michael.

Always use referential integrity. After transaction, this is the most nice RDBMS feature. A 'lovely error message' is far better than ghosts in the database, anyway you can choose between query the database before try to delete the record (when you have an oo model this is very simple to implement within an abstract layer), as well as catch the exception and display a real lovely message to the user.

Joao Morais

Michael Van Canneyt wrote

Not if all foreign keys to the table have the cascade delete option specified, then it is automatic. This is the option I most of the time use.

Thanks for the response. After I sent the message, I thought this might be an option as well. Thought I have never used it, it sounds dangerous. :-)

When you delete AdresType record with ID 1, all ADDRESSES records that have AddressType =1 will automatically be deleted.

OK, now the big question which might affect you as well. How do you translate this to tiOPF based applications? What if you delete an AddressType from the database (with cascade delete), but still have some objects (Contacts) in memory. They wouldn't know about the cascade delete. In tiOPF it's the Object that requests a delete and only for that specific object. A cascade delete can delete a lot of objects/records, not just itself. I guess some delete visitor would have to be launched as well on any loaded (in memory) objects.

Umm... I'll raise this issue in the tiOPF newsgroup as well.

Some people do this, but I recommend against it, as you end up with a lot of junk in your database if it is actively used.

This is what I'm worried about as well... Lots of waisted data lying around.

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

Joao Morais wrote

Always use referential integrity. After transaction, this is the most nice RDBMS feature. A 'lovely error message' is far better than ghosts in the database,

That's true, and I do have referential integrity enabled. A nice safe guard. I disabled it for unit testing purposes (separate database), but since, fixed up my unit tests to work with referential integrity enabled as well.

anyway you can choose between query the database before try to delete the record (when you have an oo model this is very simple

Umm... this sounds like a safer option than cascade delete. At least I wouldn't run into the 'in memory' issue mentioned in my reply to Michael.

Thanks for the response...

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

Cascade delete and 'no action' are quite different. The former is applyed when you have composition, the later when you have aggregation.

Joao Morais

Indeed.

Umm... I'll raise this issue in the tiOPF newsgroup as well.

I have the same issue as well, and didn't find a solution other than manually running a loop over all objects to see which one use the address type, and refuse to delete if there are some left. I'm assuming that this is what should happen in the case of an address type.

If you delete a person from a database, then I assume that you really want to delete everything that refers to this person as well -> the loop would have to delete all objects that refer to the person.

So the kind of action that must be taken depends on the kind of object.

This is all easily implemented in a RDBMS, usually I don't have to think of such issues, but one of the major drawbacks of object-oriented persistence frameworks IMHO.

Michael.

You shouldn't have such problem. Cascade deletes happens only with compositions. A composed object is never disposed by itself, but ony through its owner. On the other hand, an aggregated object should never be disposed by other objects, and when it is disposed, the others that reference it should say 'you cannot', stop the process and raise an exception.

Joao Morais