Two columns in Mysql with uniqueness constraint between column1 and column2

Question

Hi, Let us consider a user table: Username,email1,email2

Now, I want to make sure that no two rows in this table have any common email address. What would be the best way to ensure this ?

E.g.: If table has row: "Bill,[email protected],[email protected]", then trying to insert a row like "Billy,[email protected],[email protected]" should give an error.

Thanks in advance, Chandresh

Answer 1

Sadly, check constraints are not a feature of MySQL. Well, they are, but they're ignored.

Use a trigger, instead:

create trigger MyValidation after insert on user for each row
begin
    if (select count(*) from user where 
        email1 = new.email1
        or email1 = new.email2 
        or email2 = new.email1 
        or email2 = new.email2) > 0 then
        --I know that the delete could use this logic, but you can
        --insert other instructions here.

        delete from user where username = new.username

    end if
end

Answer 2

Maybe you could use some sort of stored procedure, but this may be something that you could check for before inserting your data.

SELECT email1, email2 FROM yourTable 
WHERE email1 LIKE %@email% or email2 LIKE %@email%

Answer 3

It looks like you're trying to force a combo between 1:1 and 1:multiple with your data model. Your better bet is to store each Username/Email combo separately. One row for "Username,email1" and another row for "Username,email2". If you have additional "Username"-related fields, those could stay in the core User table, while emails would move to a two-column Email table with a multi-column unique index on (Username,Email).