0

Is it possible to set a two way unique contraints in mysql columns ?

I have two columns like sender and receiver and I have set a composite unique constraint to these however, it only works if for example i enter 1 in sender and 2 in receiver multiple times and does not work :

if i have 1 in sender and 2 in receiver and 2 in sender and 1 in receiver

I hope my questions is clear.

php_javascript_html_dev
  • 396
  • 4
  • 11
  • 25

1 Answers1

0

This is not possible in MySQL. You can only have a unique constraint across multiple columns as you have seen, where you can exclude duplicate values of

sender=1, receiver=2

You cannot have a unique constraint that excludes the reverse of a constraint as well where if you have

sender=1, receiver=2

it also excludes

sender=2, receiver=1
davidethell
  • 11,708
  • 6
  • 43
  • 63
  • Hi @davidethell So should i use mysql queries to verify if data exists for both sender and receiver before adding new data so there is both way uniwie ? – php_javascript_html_dev Aug 31 '17 at 10:48
  • Yes, you could look up the sender and receiver before creating the record. Another alternative is to create an ON INSERT trigger that sends a failure signal to stop the insert. Try [this answer here](https://stackoverflow.com/questions/2538786/how-to-abort-insert-operation-in-mysql-trigger). – davidethell Aug 31 '17 at 19:31