0

I have table in PostgreSQL with sender_id and receiver_id and a unique index:

add_index :my_table, [:sender_id, :recipient_id], unique: true

Table will created, if sender_id: 1, and recipient_id: 2.

I can create another table, with sender_id: 2, and recipient_id: 1. But table with this ids, (but in different order) already exists.

How can I make unique for both cases?

  • sender_id: 1, recipient_id: 2
  • sender_id: 2, recipient_id: 1
  • Which DBMS are you using? – a_horse_with_no_name Feb 8 '16 at 22:38
  • 1
    You should ask database questions in a database language :) Why would you create two tables for the same sort of data? Or you mean rows there? Finally, does your requirement mean that if there is a message (or whatever you store there) from A to B, B cannot answer to A? – dezso Feb 8 '16 at 23:05
3

For Postgres and Oracle:

create unique index unique_combinations 
    on my_table (least(sender_id, recipient_id), greatest(sender_id, recipient_id));
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.