I have a relational database which has two tables that have a multiple to multiple relationship. As an example think of a user management system which allows users to belong to multiple groups for ease of assigning permissions. The table that stores information about which groups a user is a member of has a column for the user_id and a column for the group_id. The table should not allow the entry of two records with the same user_id and group_id. Is there a syntax for defining this kind of UNIQUE index in MySQL?
I'm currently using PHP to ensure that the record doesn't exist before inserting it, but I'd love to have the database coded properly to ensure this doesn't happen.
Thanks,
Michael
Web Emulsion