1

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" ?

  • 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 Feb 15 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. – Michael - sqlbot Feb 15 at 23:28
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.

|improve this answer|||||
3

I guess you can use CHECK constraint

ALTER TABLE foo 
  ADD CONSTRAINT email1_is_not_email2_CHK
    CHECK (email1 <> email2) ;
|improve this answer|||||
1

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.

|improve this answer|||||
-3
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(email1,email2);
|improve this answer|||||
  • 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 Feb 15 at 22:58

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.