pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
no dup rows
4+ messages / 4 participants
[nested] [flat]

* no dup rows
@ 2004-08-02 18:32 Jodi Kanter <jkanter@virginia.edu>
  2004-08-02 19:01 ` Re: no dup rows Peter Eisentraut <peter_e@gmx.net>
  2004-08-02 19:05 ` Re: no dup rows Bill Harper <bharper@solutionst.com>
  2004-08-02 19:09 ` Re: no dup rows Reece Hart <reece@in-machina.com>
  0 siblings, 3 replies; 4+ messages in thread

From: Jodi Kanter @ 2004-08-02 18:32 UTC (permalink / raw)
  To: pgsql-admin

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 <mailto:jkanter@virginia.edu100>

 

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: no dup rows
  2004-08-02 18:32 no dup rows Jodi Kanter <jkanter@virginia.edu>
@ 2004-08-02 19:01 ` Peter Eisentraut <peter_e@gmx.net>
  2 siblings, 0 replies; 4+ messages in thread

From: Peter Eisentraut @ 2004-08-02 19:01 UTC (permalink / raw)
  To: Jodi Kanter <jkanter@virginia.edu>; pgsql-admin

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/




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: no dup rows
  2004-08-02 18:32 no dup rows Jodi Kanter <jkanter@virginia.edu>
@ 2004-08-02 19:05 ` Bill Harper <bharper@solutionst.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Bill Harper @ 2004-08-02 19:05 UTC (permalink / raw)
  To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-admin

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 <mailto:jkanter@virginia.edu100>/
>
>  
>
Just create a primary key on the two columns, that way you get 
uniqueness and not null constraints.

-bill




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: no dup rows
  2004-08-02 18:32 no dup rows Jodi Kanter <jkanter@virginia.edu>
@ 2004-08-02 19:09 ` Reece Hart <reece@in-machina.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Reece Hart @ 2004-08-02 19:09 UTC (permalink / raw)
  To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-08-02 19:09 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-02 18:32 no dup rows Jodi Kanter <jkanter@virginia.edu>
2004-08-02 19:01 ` Peter Eisentraut <peter_e@gmx.net>
2004-08-02 19:05 ` Bill Harper <bharper@solutionst.com>
2004-08-02 19:09 ` Reece Hart <reece@in-machina.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox