Prevent duplicate rows with all non-unique columns (only with MySQL)?
- by letseatfood
Ho 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!
