MySql - how can you create a unique constraint on a combination of two values in two columns

Question

I have a problem with creating index described in answer for this question: sql unique constraint on a 2 columns combination

I am using MySql, and I received syntax error, my version of this query is as follows:

CREATE UNIQUE INDEX ON friends (LEAST(userID, friendID), GREATEST(userID, friendID));

LEAST and GREATEST functions are available in MySql, but maybe the syntax should be different? I tried to make an ALTER TABLE version, but it does not worked as well.

Answer 1

In MySQL, you can't use functions as the values for indexes.

The documentation does not explicitly state this, however, it is a basic characteristic of an index to only support "fixed" data:

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.

Generally, this "fixed" data is an individual column/field; with string-fields (such as varchar or text) you can have a prefix-index and not the entire column. Check out CREATE INDEX for more info on that.

The unique index that you're trying to create in you example will have a single record ever; that's not really a beneficial index since it doesn't help for searching the entire table. However, if you index your table on userID, friendID, using the LEAST() and GREATEST() functions in a SELECT statement will be optimized thanks to the index itself, so it may be what you're after in this case.