Two-sided UNIQUE INDEX for two columns

Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too (col2, col1)?

For example

PRIMARY KEY (col1, col2)

OR

UNIQUE INDEX (col1, col2)

If we have col1=33 && col2=54; how we can avoid INSERT of col1=54 && col2=33?

In a standard SQL dbms, you'd enforce that kind of requirement by ordering the id numbers, and using a CHECK constraint. Application code, a stored procedure, or a user-defined function is responsible for putting the id numbers in the right order.

create table friends (
  user_a integer not null,  -- references users, not shown
  user_b integer not null,  -- references users, not shown
  primary key (user_a, user_b),
  check (user_a < user_b)
);

But MySQL doesn't enforce CHECK constraints. Using MySQL, I'd still use a stored procedure (not application code) to put the id numbers in the right order. But I'd also run a query or a report every now and then to make sure that all the values for user_a were less than the values for user_b.

select *
from friends
where user_a >= user_b;

I probably wouldn't include an UPDATE statement to fix those values at first. I'd want to track down what process, application, or user was going around my stored procedure to insert data directly into the table. (You can revoke direct access to the table, and require all access to go through stored procedures. But this isn't practical in some legacy systems; too much code to rewrite. And there are alternatives.)

Unfortunately, some requirements have to be implemented as administrative procedures, like running reports.

StackExchange Database Administrators Q#14109, answer score: 6