Question 21036 in Mysql Question posted in
The official MySQL documentation can be found here.

CONSTRAINT to restrict DUPLICATE values among 2 Columns – Mysql

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.

primary_number secondary_number
123456789 987654321
********** 123456789
987654321 **********

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search