Ticket #1332930 (Feature Requests)

Eric Stadtherr estadtherr at gmail.com
Thu Aug 17 16:29:24 CEST 2006


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





More information about the Dev mailing list