Re: Advice on key design

Agreed with Anton, as PK, lpp_id is easier to be managed than (lpp_person_id
+ lpp_language_id)
One more suggestion, foreign key constraints could be setup on lpp_person_id
and lpp_language_id to link with target tables.

Thanks,
Stan

2013/7/24 Anton Gavazuk <antongavazuk(at)gmail(dot)com>

> The reason is simple - as you need the artificial PK lpp_id, then
> everything else becomes an constraint
>
> Thanks,
> Anton
>
> On Jul 24, 2013, at 0:28, JORGE MALDONADO <jorgemal1960(at)gmail(dot)com> wrote:
>
> >> In your case it would be lpp_id as PK, and
> >> lpp_person_id,lpp_language_id as unique constraint
> >>
> >> Thanks,
> >> Anton
>
> Is there a reason to do it the way you suggest?
>
> Regards,
> Jorge Maldonado
>
>
> On Tue, Jul 23, 2013 at 5:02 PM, Anton Gavazuk <antongavazuk(at)gmail(dot)com>wrote:
>
>> Hi Jorge,
>>
>> In your case it would be lpp_id as PK, and
>> lpp_person_id,lpp_language_id as unique constraint
>>
>> Thanks,
>> Anton
>>
>> On Jul 23, 2013, at 23:45, JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
>> wrote:
>>
>> > I have 2 tables, a parent (tbl_persons) and a child
>> (tbl_languages_per_person) as follows (a language table is also involved):
>> >
>> > ------------------
>> > tbl_persons
>> > ------------------
>> > * per_id
>> > * per_name
>> > * per_address
>> >
>> > --------------------------------------
>> > tbl_languages_per_person
>> > --------------------------------------
>> > * lpp_person_id
>> > * lpp_language_id
>> > * lpp_id
>> >
>> > As you can see, there is an obvious key in the child table which is
>> "lpp_person_id + lpp_language_id", but I also need the field "lpp_id" as a
>> unique key which is a field that contains a consecutive number of type
>> serial.
>> >
>> > My question is: what should I configure as the primary key,
>> "lpp_person_id + lpp_language_id" or "lpp_id"?
>> > Is the role of a primary key different from that of a unique index?
>> >
>> > With respect,
>> > Jorge Maldonado
>> >
>> >
>> >
>> >
>> >
>> >
>>
>
>