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.
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.
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 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.