MySQL make two columns UNIQUE

Question

Don't know if this is possible for MySQL because I know that it doesn't support check constraints, but what I want is to make two columns unique. Before you answer with

ALTER TABLE <table_name> ADD UNIQUE(<col1>, <col2>);

That's not what I want. I would like to ensure that col1 and col2 have unique values so if they are INTs, number "1" can exist only once between both columns, which means if col1 contains "1", col2 cannot contain "1" and "1" can only appear once in col1. Hopefully that makes sense.

I know I can do it from a php level, but right now there is a lot of code, and if I miss a spot, I don't want to destroy data integrity; I rather throw an error from the database. Some ideas that I've come across is using triggers. If someone can give me an example of using triggers to accomplish this, that would be great.

UPDATE

It might help if you knew what I was doing, then maybe you can propose a better way of doing this:

I have two fields, email and new_email. When someone changes their email address, I store it into the new_email field until the accept the change. Since email is unique because it's used as their login, I HAVE to ensure that the email is unique across both fields.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

I think you should try to reorganize your database. Let's say currently you have this:

Table: users

id   name   email          new_email
102  foo    foo@mail.com   foo2@mail.com
103  bar    bar@mail.com   bar2@mail.com
104  baz    baz@mail.com   NULL

This could be changed to:

Table: users

id   name
102  foo
103  bar
104  baz

Table: emails

user_id  is_new  email
102      0       foo@mail.com
102      1       foo2@mail.com
103      0       bar@mail.com
103      1       bar2@mail.com
104      0       baz@mail.com

You can then add a unique index on the final table on the column email.

MySQL make two columns UNIQUE

UPDATE

Sign up