X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E4E10D1B229 for ; Mon, 2 Aug 2004 16:10:07 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 70856-04 for ; Mon, 2 Aug 2004 19:09:55 +0000 (GMT) Received: from faraday.gene.com (fw-open-virt.gene.com [192.12.78.250]) by svr1.postgresql.org (Postfix) with ESMTP id EDF1CD1B241 for ; Mon, 2 Aug 2004 16:09:52 -0300 (ADT) Received: from tallac.gene.com (tallac.gene.com [128.137.187.213]) by faraday.gene.com (Switch-3.1.4/Switch-3.1.0) with ESMTP id i72J9q1O023758; Mon, 2 Aug 2004 12:09:52 -0700 (PDT) Subject: Re: no dup rows From: Reece Hart To: Jodi Kanter Cc: Postgres Admin List In-Reply-To: <410E88D6.8050906@virginia.edu> References: <410E88D6.8050906@virginia.edu> Content-Type: multipart/alternative; boundary="=-HHBa5yW31Ckb7iZF9Da0" Message-Id: <1091473791.16547.89.camel@tallac> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Mon, 02 Aug 2004 12:09:52 -0700 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200408/12 X-Sequence-Number: 14407 --=-HHBa5yW31Ckb7iZF9Da0 Content-Type: text/plain Content-Transfer-Encoding: 7bit On Mon, 2004-08-02 at 11:32, Jodi Kanter wrote: > I have a linking table that has only two fields in it and both are > foreign keys to other tables. I'd like to ensure that there are never > any duplicate rows in this table. I was planning on creating a > multi-column unique index to satisfy this requirement. Is there a more > appropriate way to accomplish this or am I on the right track?> Yep, that is the correct way to impose this constraint. I give constraints meaningful names prefixed by the table name (e.g., tablename_seq_already_in_set) so that the cause of an error is more readily identifiable. This is particularly useful for insertions into complex tables with multiple constraints. -Reece -- Reece Hart, Ph.D. rkh@gene.com, http://www.gene.com/ Genentech, Inc. 650-225-6133 (voice), -5389 (fax) Bioinformatics and Protein Engineering 1 DNA Way, MS-93 http://www.in-machina.com/~reece/ S. San Francisco, CA 94080-4990 reece@in-machina.com, GPG: 0x25EC91A0 --=-HHBa5yW31Ckb7iZF9Da0 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit _______________________________ On Mon, 2004-08-02 at 11:32, Jodi Kanter wrote:
I have a linking table that has only two fields in it and both are foreign keys to other tables. I'd like to ensure that there are never any duplicate rows in this table. I was planning on creating a multi-column unique index to satisfy this requirement. Is there a more appropriate way to accomplish this or am I on the right track?>

Yep, that is the correct way to impose this constraint.

I give constraints meaningful names prefixed by the table name (e.g., tablename_seq_already_in_set) so that the cause of an error is more readily identifiable. This is particularly useful for insertions into complex tables with multiple constraints.

-Reece
-- 
Reece Hart, Ph.D.                  rkh@gene.com, http://www.gene.com/
Genentech, Inc.                    650-225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93                   http://www.in-machina.com/~reece/
S. San Francisco, CA  94080-4990   reece@in-machina.com, GPG: 0x25EC91A0
--=-HHBa5yW31Ckb7iZF9Da0--