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