Make antisymmetric key in SQL

Question

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".

score 1 · Accepted Answer · answered Jan 24 '11 at 12:28

1

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 );

answered Jan 24 '11 at 12:28

Mike Sherrill 'Cat Recall'

91,602
17
122
185

I thought MySQL didn't support check constraints (http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-not-working) – kvista Jan 24 '11 at 12:49
Years ago, MySQL was infamous for claiming that it supported foreign key constraints. Its documentation said something like "MySQL supports foreign key constraints. All foreign key constraints are parsed and then ignored." So, you're right; that's how MySQL 5.5 still supports CHECK() constraints. If you're going to implement a constraint like this in MySQL, you'll have to write a trigger. And I'm not sure that would work, either, given MySQL's restrictions on triggers and stored functions. – Mike Sherrill 'Cat Recall' Jan 24 '11 at 13:11
Basically, you have to resort to some level of trickery (force an error instead of signaling one), but you can get it to work. They still claim what you say about CHECK constraints (Search for "ignored" in http://dev.mysql.com/doc/refman/5.5/en/create-table.html). – kvista Jan 24 '11 at 13:40

score 0 · Answer 2 · edited May 23 '17 at 11:43

Related to my comment about lack of check constraints in MySQL, a well known workaround has been the trigger. You would do the following check as part of an BEFORE INSERT ON trigger:

SELECT COUNT(*) from mytable WHERE b=NEW.a and a=NEW.b

but to make this cause a failure, you have to resort to MySQL Trickery 1.0 (previously described in TRIGGERs that cause INSERTs to fail? Possible?)

So, you would need something like the following: (tested)

delimiter |
CREATE TRIGGER t1
BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
      set @x = (SELECT COUNT(*) FROM mytable WHERE b=NEW.a and a=NEW.b);
      IF (@x > 0) THEN
         set @y = (SELECT noSuchField FROM mytable);
      END IF;
END;
|

NOTE: I'm ignoring case insensitivity here -- your question doesn't seem to require that.

Make antisymmetric key in SQL

2 Answers2