Jump to content

MySQL Multiple Column Indexes


Recommended Posts

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

Link to comment
Share on other sites

Hello,

 

can your DB-System no "primary key"?

In your group-table the group-id and the user-id should be the primary key!

This protects a duplicate tuple. If i understand you right, is it what you will.

Further, you should check, if you can use a "foreign-key". This means the user-id can only inserted if the user-id exists in the user-table.

What DB-System is used ?

 

 

Hagen

Link to comment
Share on other sites

  • 3 weeks later...

Thanks for your help.

 

I'm using MySQL. I would like to code it to work with version 3.23.44 but if I need to use 4.0.1 or higher that is fine too.

 

In order to use Foriegn Keys I guess I would need to use InnoDB tables. Right now my tables are MyISAM. But the benifits of checking with the keys in other tables would probably pay off in the long run. Both the user_id and group_id would be foreign keys.

 

Could you give me a SQL code example of how to define the keys for the tables?

 

I don't quite understand how to define them. For example lets say there are two columns A & B which need indexing. How would I define two separate unique indexes for each column? How would I define a single unique index for the combination of the two columns?

 

Thanks

 

 

Link to comment
Share on other sites

  • 2 months later...

Try adding a third column to the table, which would act as the primary key. In this column, you would insert the concatenated values of the user id and the group id. That way, you can never enter the user twice with the same group. For example:

 

table:

 

create table groups(id int(12) not null, uid, int (12) not null, group int(12) not null, primary key(id), key users(u_id));

 

Then, whenever you insert into the table, you would provide three values

 

id = $uid . $grp_id

uid = $uid

group = $group

 

And there you have it. I hope that helps

 

-Chris

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.