Il.add_index question

Rails
1

I have a table that is modeling a friendship between two players. I’d
like it so that there can only be one record for this friendship in
the table. I have added an index to the table to make sure that a
record is unique like so …

id1 id2
1 2
1 2 # Error, record isn’t unique

add_index(:friendships, [:friend_id1, :friend_id2], :unique => true)

I would also like to add an index to take care of the following
situation…

id1 id2
1 2
2 1 # Error, since it’s the same as the first record

How would I do this?
Thanks.
Steve

2

2009/10/13 steveb56 [email protected]:

add_index(:friendships, [:friend_id1, :friend_id2], :unique => true)

I would also like to add an index to take care of the following
situation…

id1 Â id2
1 Â Â 2
2 Â Â 1 Â # Error, since it’s the same as the first record

Could you setup your code so that for a friendship record id1 is
always less than id2? If there is no way in the code that id1=2 and
id2=1 can occur then you do not need to protect against it.

Colin

3

Colin L. wrote:

2009/10/13 steveb56 [email protected]:

add_index(:friendships, [:friend_id1, :friend_id2], :unique => true)

I would also like to add an index to take care of the following
situation…

id1 Â id2
1 Â Â 2
2 Â Â 1 Â # Error, since it’s the same as the first record

Could you setup your code so that for a friendship record id1 is
always less than id2? If there is no way in the code that id1=2 and
id2=1 can occur then you do not need to protect against it.

And if you really want to make this bulletproof, you can enforce id1 <
id2 with a check constraint in the DB.

Colin

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

4

Yeah that’s true, thanks.

Steve

On Tue, Oct 13, 2009 at 9:17 AM, Marnen Laibow-Koser <

5

I see what your saying yeah, that could work. Before I add it to the
table, I would get it in the form id1 < id2 & then my previous index
would work the same way essentially catching both of the cases I was
describing.

Thanks for the help.
Steve