I have 2 columns col1 and col2

is it possible to make a constraint to not be able to add the same email two times in this columns, only using indexes?

The Question has been edited to: "Is it possible to define a constraint so as to be unable to add the same email twice in these columns for the same record, using only indexes?"

But it is my fault, I did not give enough details. When I have a unique column. I can not repeat a entry in this column. What I need is to expand this "unique" to a second column, so that any entry made in the first column, should not be possible entered to the second column at all.

I can do it using a external program by consequently adding the entries for this two columns in a array and always checking the array before entering something to this two columns. But the solution a joke, it is sub-optimal, because by extrapolation we do not need databases any more we can use arrays.

Is this possible using just mysql, or do I need "my array" ?

  • Have you tried to Google your title?
    – Eric
    Feb 15, 2020 at 0:20
  • 1
    What do you mean? Is the second email address for person A allowed to be the same as the first or second email address for person B? Feb 15, 2020 at 22:59
  • 1
    Column names like thing1, thing2, ... thingN are often a sign of a design that should have been implemented differently, because -- depending on who you ask -- the design may be a violation of the "no repeating groups" rule of 1NF which makes things like this more difficult. Why are there two email columns? The answer to that question may help shed light on appropriate solutions. Feb 15, 2020 at 23:28

4 Answers 4


Today you allow 2 emails; tomorrow you will allow 3. That begs for having a separate table. Such will let you use UNIQUE.

A schema design rule: Don't use multiple columns to implement an "array"; use a separate table.


I guess you can use CHECK constraint

  ADD CONSTRAINT email1_is_not_email2_CHK
    CHECK (email1 <> email2) ;

Yes, you can achieve this with the CHECK option in the CREATE/ALTER TABLE statement, but only for MySQL since v8.0.16 and MariaDB since v10.2.1.

Reference: CHECK Constraints (MySQL Dev Documentation) and CONSTRAINT (MariaDB KB)


Create Table

create table emlchk
check (email1 <> email2),
emlchk_id int not null auto_increment,
email1 varchar(100),
email2 varchar(100),
primary key (emlchk_id)

Insert First Records

insert into emlchk 
(email1, email2) 
('[email protected]', '[email protected]')

Insert will fail with message:

Check constraint 'emlchk_chk_1' is violated.

Check Contents of Table

select * from emlchk

Yes, the table is still empty.

Insert Valid Records

insert into emlchk 
(email1, email2) 
('[email protected]', '[email protected]'),
('[email protected]', ''),
('','[email protected]')

...and verify:

select * from emlchk

...which returns:

emlchk_id | email1                       | email2                      
--------: | :--------------------------- | :---------------------------
        1 | [email protected]     | [email protected]    
        2 | [email protected] |                             
        3 |                              | [email protected]

Update Second Column with Same Data

update emlchk set email2 = '[email protected]' where email1 = '[email protected]'


Check constraint 'emlchk_chk_1' is violated.

Update Second Column with Different Data

update emlchk set email2 = '[email protected]' where email1 = '[email protected]' 

This seems to work.

Complete Example

The complete example can be found on db<>fiddle.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(email1,email2);
  • 1
    Err, no. This makes the combination of the two email addresses, and in that order, unique. So one could duplicate email addresses as long as the second email address was different. Feb 15, 2020 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.