Prevent row duplication with all non-unique columns (only with MySQL)?

Question

How to prevent the creation of a duplicate row in a table with two columns, none of which are unique? And can this only be done using MySQL, or does verification need to be done using my PHP script?

Here is the CREATE query for the table in question (there are two other tables, usersand 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 it was 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.

Thank!

Answer 1

Define the columns user_idand role_idas 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;

, , , .

, /.

Answer 2

, ,

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

Answer 3

Once in college, a DB professor told me: “Each individual table must have a primary key”

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