Hello all,

I have an app that I am building where I am creating a many to many table used to associate roles to a user.

user_id INT
role_id INT

Using MYSQL how would I go about making it so I could ensure that I have 1 instance of a record and not have duplicates.

user_id | role_id
1234 | 5678
1234 | 5678 # produces an error and doesn't get created

I am told that a possible solution is to set both columns to primary keys but I want to know if there is another way to do this that is better?

I am open to any suggestions.

Thanks,

Since user_id is the primary key of one table make it a foreign key in the second table with role_id the primary key. You can also make (user_id, role_id) an unique key.

What he said, the unique key part.

But you can make it a compound primary key (user_id, role_id) too.

You can also use REPLACE INTO instead of INSERT INTO, which enforces the single instance without throwing an error if you try to insert a duplicate.

*** Afterthought...

Any reason you do not want to make them into a compound primary key?

It costs nothing in this case in terms of additional indexing since they are unique by definition.

Yes, it is clearly a composite primary key. (Most likely the OP didn't know that such thing is allowed.)

You can make it a compound key. Check also UPDATE ... ON DUPLICATE KEY ...
For many-to-many relationships you can also use a so called resolver table. One table with users, one table with roles, and one intermediate table which holds (say) user_ids and role_ids. http://en.wikipedia.org/wiki/Junction_table.

jlinkels

It all depends on what YOU want to do.

Eg:
what aboutI mean is the table a master record (1 row per user) or a history record

If OK, ie. 1 user having multiple roles, then compound key is OK.

Hi Guys,

Thanks for all the responses.

@AnanthaP - yes 1 user would be able to have multiple roles so your description what I am attempting to do is correct.

@jlinkels - I didn't know UPDATE ... ON DUPLICATE KEY ... existed. I certainly will look into that. Also to your post, that is how I have the many to many relationship set up. I have a users table with all the user info and a roles table with all the role info then I have a roles_users table which has the id of both tables to link them together.

@astrogeek - no particular reason not to make this a compound key if that is the best solution. Mainly I am trying to get an idea of what my options are aside from what was suggested to me. From what i am gathering in this thread, using a compound primary key seems the best way to go with this.

I appreciate everyones input on my question. It looks like the way I will be going with this is looking at making a compound primary key and use that. I have never done a compound primary key before so I will be searching the the MYSQL docs to see what they have on the subject.

http://stackoverflow.com/questions/5...ary-keys-mysql seems to contain what you need.

If you read down the entire discussion, it would seem to clarify all your doubts. The approach suggested in not to actually have a derived field for the compund/composite key but to declare the primary key as per the example below.

CREATE TABLE INFO (
t1ID INT,
t2ID INT,
PRIMARY KEY (t1ID, t2ID)
)


OK