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