Advice on key design

From: JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Advice on key design
Date: 2013-07-23 21:44:20
Message-ID: CAAY=A7_ZKoCRjP19oLRYfQeV1=Q=c-3cX+qDmPfZC8_o_B8msQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Anton Gavazuk 2013-07-23 22:02:43 Re: Advice on key design
Previous Message ldrlj1 2013-07-22 14:53:01 Re: table constraint on two columns