Jump to content
  • Advertisement
Sign in to follow this  
JDev

[web] MYSQL multiple fields for primary key?

This topic is 4649 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi, I'm using mysql and I want to use two fields together as the primary key like CREATE TABLE test ( project CHAR(30) NOT NULL, username CHAR(30) NOT NULL, INDEX name ( project, username), PRIMARY KEY (name) ); But this doesn't work, what do I need to do to make two fields together work as the primary key.....? thanks

Share this post


Link to post
Share on other sites
Advertisement
And, I think making them primary keys will automatically create indices on those fields, so you probably don't need to specify it explicitly.

Share this post


Link to post
Share on other sites
In fact, if you have a primary key, the fields of that primary key will be stored in each index you create on the table (because that's how it finds the record in question). Thus, if your primary key contains the fields you actually want to read back in a query, the query can run entirely in the index space, and doesn't need to seek to the table, which means less disk seeks -- but larger indices.

Share this post


Link to post
Share on other sites
joanusdmentia's suggestion will work fine.

But can I recommend that you do not use two strings as the primary key.

It's normally a bad idea to use a string as the primary key, because it probably means something and might need to be changed. It's possibly a worse idea to use two strings.

Personally I'd keep the users in a separate table, and put the user_id as a foreign key in your projects table (bear in mind that a foreign key can be part of a primary key if you want).

Then if you want the project names to be unique per user, just stick a unique index on (userid, project) columns.

Mark

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!