如何在MySQL中防止所有非唯一列的重复行？

Question

如何防止在一个有两列且没有唯一标识的表中创建重复行？这能够仅使用MySQL完成还是需要通过PHP脚本进行检查？

以下是问题表的CREATE查询（还有另外两个表，users和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;

如果执行以下查询超过一次，我希望会出现错误提示：

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

请不要将位掩码作为答案推荐。

谢谢！

- Mike Moore

Answer 1

在不改变原本定义为键的内容的情况下，您可以添加一个约束条件

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

- Stephen P

Answer 2

将user_id和role_id列定义为该表的主键。

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;

当一个键使用两个或更多列时，它被称为复合键，就像这个例子。

如果主键已经定义，您需要考虑使用唯一约束/索引。

- OMG Ponies

Answer 3

我在大学时，一位数据库教授告诉我：“每个表都必须有一个主键”

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

- Ben