Search code examples
sqlmysqluniqueasymmetric

Make antisymmetric key in SQL

ALTER TABLE `test` ADD UNIQUE (
`a` ,
`b`
);

I want that after creating this unique key, it won't be possible to add B,A.

For example: "Michael", "Jackson", won't allow inserting later "Jackson", "Michael".

Solution

  • I'm going to assume you're application deals with something besides real names. (Because "Jackson Michael" is a legitimate name in English. So are "John David" and "David John".)

    The simplest way is to use a CHECK() constraint to enforce alphabetical order between the two columns.

    alter table test
add constraint test_alpha_order
check (a < b);

    But note that you might run into problems with case sensitivity, too. That is, your dbms might believe that {'Jackson', 'Michael'} and {'jackson', 'michael'} are two different, valid values. If case sensitivity is a problem, the most common solution I've seen is to enforce lowercase values, like this.

    alter table test
add constraint test_alpha_order
check (a = lower(a) and
       b = lower(b) and
       a < b );