Mysql – combined contents of 2 columns can’t contain duplicates

constraintMySQLmysql-8.0

This is a hard question to ask. I find it difficult to put into words.

I have two columns where the content of column A can not appear in column B and vice versa. So, the combined contents of both columns needs to be unique.

In other words: I want any value that already exists in column A not to be allowed in either A or B. Likewise for B (no duplicates in B or A).

Is there a way to do this in MySQL with only a table schema change?

I also don't want to use triggers to prevent insertions.

MySQL version 8.0.18

Best Answer

The only way that I can think of to do this (without using triggers) is to use Firebird. Doesn't really answer the question (MySQL), but is interesting functionality to explore - see the fiddle here!

CREATE TABLE test
(
  column_A INTEGER NOT NULL,
  column_B INTEGER NOT NULL,
  CONSTRAINT a_uq UNIQUE (column_A),
  CONSTRAINT b_uq UNIQUE (column_B),
  CONSTRAINT a_ne_b_ck CHECK (column_A != column_B),
  CONSTRAINT a_ni_b_ck CHECK (column_A NOT IN (SELECT column_B FROM test)),
  CONSTRAINT b_ni_a_ck CHECK (column_B NOT IN (SELECT column_A FROM test))
);

It does make the table declaration more complex (con) but with the massive pro of not having to maintain triggers oneself. It would appear from the error messages that Firebird uses triggers in the background - better that the server maintain them than me!

--
-- The next two INSERTs work (as one would expect)
--
INSERT INTO test VALUES (2, 4);
INSERT INTO test VALUES (3, 5);

Then, to test (standard functionality first):

--
-- This doesn't work - breaches UNIQUE constraint on column_A
-- Look at the quality of the error message!
--
INSERT INTO test VALUES (2, 7);  -- 2 is a dup in column_A

fails with the error:

violation of PRIMARY or UNIQUE KEY constraint "A_UQ" on 
table "TEST" Problematic key value is ("COLUMN_A" = 2)

Look at the quality of that error message - even telling you the offending value!

Then we try (again standard CHECK constraint stuff):

--
-- This doesn't work - breaches the column_A != column_B CHECK. Easy, peasy!
-- All RDBMS do this - even MySQL now since version 8!
--
INSERT INTO test VALUES (4, 4);

Error message:

Operation violates CHECK constraint A_NE_B_CK on view or 
table TEST At trigger 'CHECK_1'


Now, we test the SQL within the CHECK constraint!

--
-- This is where it gets interesting! Notice that it **_won't_** allow a value
-- that's in column_B be inserted into column_A! Pretty cool!
--
INSERT INTO test VALUES (5, 9);

gives the error:

Operation violates CHECK constraint A_NI_B_CK on view or table 
TEST At trigger 'CHECK_3'

The SQL in the CHECK constraint works the other way round also - see the fiddle. Note the part of the error message where it says: table TEST At trigger 'CHECK_3' - triggers at work in the background!

This is really powerful stuff! It's such a shame that Firebird isn't more popular/widespread - it really deserves the no. 2 spot in F/LOSS database ecosphere!

Incredibly, MySQL only recently introduced CHECK constraints (only at least 25 years after all its other major - and not so major - competitors). But, now it appears to be making serious efforts to catch up! But nobody else (AFAIK) has SQL in CHECK constraints, not even the mighty Oracle, MS SQL Server or PostgreSQL (note to self - suggest this on the lists!).

Related Question