Set as unique a couple of fields in mysql

Question

I want to set as unique a couple of fields in mysql

ex: field1 = 10 field2 = 30

I don't want duplicate for this couple of values. How do I set this in phpmyadmin?

thanks

score 1 · Answer 1 · answered Nov 18 '13 at 18:28

1

ALTER IGNORE TABLE mytable
ADD UNIQUE (field1 );

ALTER IGNORE TABLE mytable
ADD UNIQUE (field2 );

Or if you want a combined unique index:

ALTER IGNORE TABLE mytable
ADD UNIQUE (field1,field2 )

answered Nov 18 '13 at 18:28

Mihai

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER IGNORE TABLE mytable ADD UNIQUE (field1,field2 )' at line 1 0.000 sec – Julien Apr 29 '16 at 08:14

score 1 · Answer 2 · edited May 23 '17 at 12:07

1

ALTER TABLE mytable ADD UNIQUE my_unique_index(field1,field2);

Explanation:

As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error.

A UNIQUE constraint contains an index definition

edited May 23 '17 at 12:07

Community

answered Apr 29 '16 at 08:17

Julien

Please put some effort in your answer. – Box Box Box Box Apr 29 '16 at 08:55
Sorry, I thought this answered perfectly the question, and I didn't know I had to explain why other answer might be wrong and irrelevant. So I will update. – Julien Apr 29 '16 at 09:03

2 Answers2