Subject Re: [firebird-support] restrict delete using before trigger (not working as expected)
Author anirudh dutt
On Wed, 27 Oct 2004 10:48:38 +1000, Helen Borrie <helebor@...> wrote:
> At 03:42 AM 27/10/2004 +0530, you wrote:
> >hi
> >i've got a table with a 2 column primary key: (id, part).
> My first question in this puzzle is:
> why is this unstable "part" element in the primary key? If "id" on its own
> is not unique, why is that so?

well, "id" would be unique for each item...which has several "part"s.
this is comparable to a phone number list. to keep things flexible
(i.e. any number of phone numbers for one person) and to normalize. it
would generally have 7-8 parts but it varies and can go higher. that
was my attempt at avoiding bad db design.

my option would be to keep 8-10 cols in the main table where id is
unique...which isn't good. there will always be that 1 in 50 scenario
where i'd need 15 parts, so it makes sense to use another table

so "id" by itself isn't unique in that table but the combo is. also,
in any usage with the table i'd either use just id (first part of the
index) or id and part in that order, so the pk index gets used.