no dup rows

[Jodi Kanter]

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

[Bill Harper]

Post by Jodi Kanter
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
[phone]

Just create a primary key on the two columns, that way you get
uniqueness and not null constraints.

-bill


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Reece Hart]

Post by Jodi Kanter
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

[Peter Eisentraut]

Post by Jodi Kanter
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.