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

Define both the user_id and role_id columns as the primary key for the table.

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

Key is called a composite when it uses two or more columns, like this case.

If the primary key is already defined, you need to look at using unique constraints/indexes.

Answer 2

Once in college a DB professor told me: "Every single table must have a primary key"

ALTER TABLE users_roles ADD PRIMARY KEY (user_id, role_id);

Answer 3

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);