Unique over 2 columns

Question

I have 2 columns col1 and col2

INITIAL QUESTION:
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" ?

Answer 1

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.

Answer 2

I guess you can use CHECK constraint

ALTER TABLE foo 
  ADD CONSTRAINT email1_is_not_email2_CHK
    CHECK (email1 <> email2) ;

Answer 3

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: 13.1.20.7 CHECK Constraints (MySQL Dev Documentation) and CONSTRAINT (MariaDB KB)}

Example

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) 
values
('[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) 
values
('[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]'

Returns:

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.

Answer 4

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(email1,email2);