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