Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V1uFk-0003TV-TZ for pgsql-sql@arkaria.postgresql.org; Wed, 24 Jul 2013 08:16:57 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1V1uFj-00061z-UX for pgsql-sql@arkaria.postgresql.org; Wed, 24 Jul 2013 08:16:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V1uFh-00061q-ST for pgsql-sql@postgresql.org; Wed, 24 Jul 2013 08:16:53 +0000 Received: from mail-vb0-x229.google.com ([2607:f8b0:400c:c02::229]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V1uFZ-0005ig-Ib for pgsql-sql@postgresql.org; Wed, 24 Jul 2013 08:16:53 +0000 Received: by mail-vb0-f41.google.com with SMTP id p13so6161065vbe.14 for ; Wed, 24 Jul 2013 01:16:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=references:from:in-reply-to:mime-version:date:message-id:subject:to :cc:content-type; bh=q9v729lm3FbD64j+hqD7CXwsXcrvnTGdq9sgRXAwpWs=; b=RwHxJoivomJD69cKe2fOxwygh0sE+OmzuZcMOiWHGvIK+ED6996q0M+pGy5D/1KCcb 84+kVVU3jjuIWWB7WnWo4eXNEpt20ujR+L0MYjOI4Xot0lkuPUnVtMt8EOS+HCR/vigu RN4Us6nwEPhEr7etVVU3pwFuAlbsSXWu6KbbnZGDKS3pPDpyF7zmOSGf8FFWDA301klM lJFoTkpX7CIc0ZbJhynr3TeGQijp7o6W/Aa1OT/kC3b6bUXXF6+z4KgmVkmCb0BnUKC9 sR5Myy73IYQjSLJgUrtoTPbwEMgz6SVdYh2N+HVQMZKKdJp5h25ujFDjNo1h32fDNYZf FDUQ== X-Received: by 10.58.207.135 with SMTP id lw7mr13308626vec.92.1374653803988; Wed, 24 Jul 2013 01:16:43 -0700 (PDT) References: <3311319936507639588@unknownmsgid> From: Anton Gavazuk In-Reply-To: Mime-Version: 1.0 (1.0) Date: Wed, 24 Jul 2013 10:16:45 +0200 Message-ID: <1583742764256639694@unknownmsgid> Subject: Re: Advice on key design To: JORGE MALDONADO Cc: "pgsql-sql@postgresql.org" Content-Type: multipart/alternative; boundary=047d7b6d7c5cad0bb904e23d8705 X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --047d7b6d7c5cad0bb904e23d8705 Content-Type: text/plain; charset=ISO-8859-1 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 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 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 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 > > > > > > > > > > > > > --047d7b6d7c5cad0bb904e23d8705 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
The reason is simple - as= you need the artificial PK =A0lpp_id, then everything else becomes an cons= traint

Thanks,
Anton

On Jul 24, 2013, at 0:2= 8, JORGE MALDONADO <jorgemal19= 60@gmail.com> wrote:

>> In your case= it would be lpp_id as PK, and
>> lpp_pe= rson_id,lpp_language_id as unique constraint
>>
>> Thanks,
>> Anton
Is there a reason to do it the way you sugge= st?

Regards,
Jorge Maldonado


On Tue, Jul 2= 3, 2013 at 5:02 PM, Anton Gavazuk <antongavazuk@gmail.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@gmail.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 &q= uot;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 n= umber 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
>
>
>
>
>
>

--047d7b6d7c5cad0bb904e23d8705--