I need to put a constraint which will restrict DUPLICATE entries in combination of 2 columns.

So I have a customers table, with the below mentioned columns

id, first_name, last_name, date_of_birth, gender, email_address, primary_number, secondary_number.

What I am expecting is to have primary_number value unique for 2 columns. i.e. primary_number & secondary_number.

Eg.

So, "123456789" should not be allowed in secondary_number, if it is already available in primary_number

As well, "987654321" should not be allowed in primary_number, if it already available in secondary_number

CodePudding user response：

I understand your question in that way only "pairs" like for example 123456789, 987654321 and 987654321, 123456789 should pre prevented by the unique constraint. If the row 123456789, 987654321 already exists, I assume rows like 111111111, 987654321 or 123456789,222222222 should still be allowed.

If this is correct so far and if your two columns are numbers, we can use LEAST and GREATEST:

ALTER TABLE yourtable 
  ADD CONSTRAINT uniqueNumbers UNIQUE KEY
  ((LEAST(primary_number, secondary_number)), 
   (GREATEST(primary_number, secondary_number)));

This will only prevent such duplicated "pairs".

Try out here

If this assumption is incorrect and also the other rows should be prevented, I would use a trigger here rather than constraints or use Akina's idea if possible.

CodePudding user response：

If your MySQL version is 8.0.17 or higher then you may use unique multivalued index.

DEMO

CREATE TABLE test (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    val1 INT UNSIGNED NOT NULL,
    val2 INT UNSIGNED NOT NULL,
    UNIQUE vals( (CAST(JSON_ARRAY(val1, val2) AS UNSIGNED ARRAY)) )
    );

INSERT INTO test (val1, val2) VALUES 
(123,234), (345,456);

Records: 2  Duplicates: 0  Warnings: 0

INSERT INTO test (val1, val2) VALUES 
(123,567);

Duplicate entry '[123, 56' for key 'test.vals'

INSERT INTO test (val1, val2) VALUES 
(678,345);

Duplicate entry '[345, 67' for key 'test.vals'

fiddle