JohnathanKong JohnathanKong - 1 year ago 99
MySQL Question

MySQL make two columns UNIQUE

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.


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.

Answer Source

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
103  bar
104  baz   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
102      1
103      0
103      1
104      0

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download