[Lazarus] [OT] How to delete a record/object being used

[Michael Van Canneyt]

On Tue, 9 Sep 2008, Graeme Geldenhuys wrote:

> 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.

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.