Martin, We're talking about the same structure, and I completely agree with you about the contact_info->contact foreign key. I wouldn't include the cascading delete on the second FK, though (the contact_info->types reference), since the types contents should be relatively static and you want the database to prevent you from deleting types if there are referring contact_info rows.
Thoughts? -Eric > On Tue, 15 Aug 2006, Eric Stadtherr wrote: > >> Martin, >> >> I understand the intent of the CASCADE, I was just under the impression > that >> the "contact_type" table in your first example (or the > contact_field_type >> table in my version) were "static" tables that defined the existence of >> certain field types (phone number, email, birthday, first name, last > name, >> etc.). The type column in the contact table would then reference the PK > of >> the "type" table (through the FK constraint) to describe which type of > field >> was contained in that row. You then wouldn't want to delete your type > entry >> if there were no referencing contact entries. >> Does this make sense? > > Are we talking about the same structure? > > <sql code> > alter table contact_info add foreign key (contact_id) references contact > (contact_id) on delete cascade on update cascade > alter table contact_info add foreign key (contact_type) references types > (type_id) on update cascade on delete cascade > </sql code> > > In this case, the contact info (your phone numbers, email addresses, etc.) > > will get deleted, IF the contact is deleted. That's a good policy, else > you'll have garbage in the DB. > > -- > 21:50:04 up 2 days, 9:07, 0 users, load average: 0.92, 0.37, 0.18 > --------------------------------------------------------- > Lic. Martín Marqués | SELECT 'mmarques' || > Centro de Telemática | '@' || 'unl.edu.ar'; > Universidad Nacional | DBA, Programador, > del Litoral | Administrador > -------------------------------------------------------
