CODEWITHSUNDEEP
mysql
JohnathanKong
JohnathanKong

Reputation: 1307

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.

Upvotes: 3

Views: 2783

Answers (5)

Frank
Frank

Reputation: 23

Have you solved your problem? I have encountered this problem too, and finally I solve my problem with trigger, following your idea. Here is my sql:

delimiter |

CREATE TRIGGER unique_AB BEFORE INSERT ON test
  FOR EACH ROW
  BEGIN
         DECLARE msg varchar(200);  
     DECLARE flag int;
     set flag = (select count(*) from test where A = new.A or B = new.A or A = new.B or B = new.B);
     IF flag > 0 THEN
        set msg = "column duplicate!!!";  
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;  
     END IF;
  END;
|

my table is as follow:

CREATE TABLE `test` (
  `A` varchar(255) DEFAULT NULL,
  `B` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

in the trigger, I made a select first and judge if there is already a value that is equals to my new line, and this worked for me.

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 839224

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    [email protected]   [email protected]
103  bar    [email protected]   [email protected]
104  baz    [email protected]   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       [email protected]
102      1       [email protected]
103      0       [email protected]
103      1       [email protected]
104      0       [email protected]

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

Upvotes: 1

Chandu
Chandu

Reputation: 82943

As per you update on the question a database constraint is not a valid option, because you want to store the email for a while in both the fields and then accept it once the user accepts it. It looks more like a logic that needs to be implemented in the application business logic than the database

Upvotes: 0

Raj More
Raj More

Reputation: 48058

Solution

  1. Create a table MakeColsUnique with one column ColumnBoth
  2. Create a Primary Key on ColumnBoth
  3. All all values from Col1 and Col2 to this table (if you have existing duplicates, this will fail)
  4. Add a trigger on OriginalTable on INSERT or UPDATE to insert the value from Col1 and Col2 into the new table MakeColsUnique

If the value has already been inserted, the insert or update will fail.

Upvotes: 2

Vinay Pai
Vinay Pai

Reputation: 7758

You can't enforce that with a key constraint. Honestly the requirement sounds a little bit odd, and I think you're probably better off extracting col1 and col2 into a separate table, say cols.

Upvotes: 0

Related Questions