How to guarantee the uniqueness of a record using a complex key?

Question

MySQL

There is such a simple table, with 3 foreign keys. How can I make it impossible to re-create an entry with the same values ​​for these three keys? Create a complex key based on them?
And how to do it in the Workbench environment, just additionally specify each one as a primary key?

Answer 1

CREATE TABLE user_roles(
   user_id INT NOT NULL,
   role_id INT NOT NULL,
   PRIMARY KEY(user_id,role_id),
   FOREIGN KEY(user_id) REFERENCES users(user_id),
   FOREIGN KEY(role_id) REFERENCES roles(role_id)
);

www.mysqltutorial.org/mysql-primary-key
https://dev.mysql.com/doc/refman/8.0/en/multiple-c...

Answer 2

IMHO, wrong requirement. What prevents a person in one organization from transferring, say, from programmers to loaders, and then back to programmers? Here you have a double of this trio of fields.