0

I have 2 columns email1 and email2.

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?

  • Have you tried to Google your title? – Eric Feb 15 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? – Colin 't Hart 2 days ago
  • 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. – Michael - sqlbot 2 days ago
3

I guess you can use CHECK constraint

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

Yes, you can achieve this with the CHECK option in the CREATE/ALTER TABLE statement, but only for MySQL 8.0.

Reference: 13.1.20.7 CHECK Constraints (MySQL Dev Documentation)

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
('something@domain.com', 'something@domain.com')

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
('something_one@domain.com', 'something_two@domain.com'),
('something_one_one@domain.com', ''),
('','something_two_two@domain.com')

...and verify:

select * from emlchk

...which returns:

emlchk_id | email1                       | email2                      
--------: | :--------------------------- | :---------------------------
        1 | something_one@domain.com     | something_two@domain.com    
        2 | something_one_one@domain.com |                             
        3 |                              | something_two_two@domain.com

Update Second Column with Same Data

update emlchk set email2 = 'something_one_one@domain.com' where email1 = 'something_one_one@domain.com'

Returns:

Check constraint 'emlchk_chk_1' is violated.

Update Second Column with Different Data

update emlchk set email2 = 'something_one_two@domain.com' where email1 = 'something_one_one@domain.com' 

This seems to work.

Complete Example

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

-3
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(email1,email2);
New contributor
Koteswara Rao is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 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. – Colin 't Hart 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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