Prevent duplicate rows with all non-unique columns (only with MySQL)?

Question

How do I prevent a duplicate row from being created in a table with two columns, neither of which are unique? And can this be done using MySQL only, or does it require checks with my PHP script?

Here is the CREATE query for the table in question (two other tables exist, users and roles):

CREATE TABLE users_roles (
user_id INT(100) NOT NULL,
role_id INT(100) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (role_id) REFERENCES roles(role_id)
) ENGINE = INNODB;

I would like the following query, if executed more than once, to throw an error:

INSERT INTO users_roles (user_id, role_id) VALUES (1, 2);

Please do not recommend bitmasks as an answer.

Thanks!

Answer 1

Without changing what's otherwise defined as your keys, you can add a constraint

ALTER TABLE users_roles ADD CONSTRAINT UNIQUE (user_id, role_id);