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. _______________________________________________ Lazarus mailing list [email protected] http://www.lazarus.freepascal.org/mailman/listinfo/lazarus
