From jkanter@virginia.edu Mon Aug 2 18:33:10 2004 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 87FD7D1B1EC for ; Mon, 2 Aug 2004 15:33:10 -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 55287-02 for ; Mon, 2 Aug 2004 18:33:02 +0000 (GMT) Received: from cms.mail.virginia.edu (fork2.mail.Virginia.EDU [128.143.2.192]) by svr1.postgresql.org (Postfix) with ESMTP id 4FA62D1B1DF for ; Mon, 2 Aug 2004 15:32:58 -0300 (ADT) Received: from virginia.edu (d-137-133-131.bootp.Virginia.EDU [137.54.133.131]) by cms.mail.virginia.edu (Postfix) with ESMTP id B39FA1680F0 for ; Mon, 2 Aug 2004 14:32:57 -0400 (EDT) Message-ID: <410E88D6.8050906@virginia.edu> Date: Mon, 02 Aug 2004 14:32:54 -0400 From: Jodi Kanter User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Postgres Admin List Subject: no dup rows Content-Type: multipart/alternative; boundary="------------060001020709010703090909" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=HTML_40_50, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: * X-Archive-Number: 200408/9 X-Sequence-Number: 14404 This is a multi-part message in MIME format. --------------060001020709010703090909 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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?> thanks. Jodi -- _______________________________ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 jkanter@virginia.edu --------------060001020709010703090909 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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?>
thanks.
Jodi
--
_______________________________

_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu

 

--------------060001020709010703090909-- From peter_e@gmx.net Mon Aug 2 19:02:02 2004 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 C40B4D1B258 for ; Mon, 2 Aug 2004 16:02:02 -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 62939-09 for ; Mon, 2 Aug 2004 19:01:58 +0000 (GMT) Received: from mail.gmx.net (pop.gmx.net [213.165.64.20]) by svr1.postgresql.org (Postfix) with SMTP id D86F9D1B248 for ; Mon, 2 Aug 2004 16:01:54 -0300 (ADT) Received: (qmail 10861 invoked by uid 65534); 2 Aug 2004 19:01:55 -0000 Received: from dsl-082-082-234-070.arcor-ip.net (EHLO colt.pezone.net) (82.82.234.70) by mail.gmx.net (mp024) with SMTP; 02 Aug 2004 21:01:55 +0200 X-Authenticated: #495269 From: Peter Eisentraut To: Jodi Kanter , Postgres Admin List Subject: Re: no dup rows Date: Mon, 2 Aug 2004 21:01:44 +0200 User-Agent: KMail/1.5.1 References: <410E88D6.8050906@virginia.edu> In-Reply-To: <410E88D6.8050906@virginia.edu> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200408022101.44412.peter_e@gmx.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/10 X-Sequence-Number: 14405 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?> In "linking tables" you should usually make the entire set of columns the primary key. This would solve your problem. -- Peter Eisentraut http://developer.postgresql.org/~petere/ From bharper@solutionst.com Mon Aug 2 19:05:54 2004 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 4BC8CD1B21B for ; Mon, 2 Aug 2004 16:05:54 -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 69801-01 for ; Mon, 2 Aug 2004 19:05:51 +0000 (GMT) Received: from devel.solutionst.com (216-107-217-154.solutionst.cust.seg.NET [216.107.217.154]) by svr1.postgresql.org (Postfix) with ESMTP id 7A879D1B1CD for ; Mon, 2 Aug 2004 16:05:48 -0300 (ADT) Received: from solutionst.com (unknown [192.168.1.96]) by devel.solutionst.com (Postfix) with ESMTP id 3D443F6902; Mon, 2 Aug 2004 15:05:52 -0400 (EDT) Message-ID: <410E9086.3060204@solutionst.com> Date: Mon, 02 Aug 2004 15:05:42 -0400 From: Bill Harper User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Jodi Kanter Cc: Postgres Admin List Subject: Re: no dup rows References: <410E88D6.8050906@virginia.edu> In-Reply-To: <410E88D6.8050906@virginia.edu> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/11 X-Sequence-Number: 14406 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?> > thanks. > Jodi > -- > > /_______________________________ > //Jodi L Kanter > BioInformatics Database Administrator > University of Virginia > (434) 924-2846 > jkanter@virginia.edu / > > > Just create a primary key on the two columns, that way you get uniqueness and not null constraints. -bill From reece@in-machina.com Mon Aug 2 19:10:07 2004 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--