CODEWITHSUNDEEP
sqlmysqldatabase
mike
mike

Reputation: 8141

MySQL Lookup table and id/keys

Hoping someone can shed some light on this: Do lookup tables need their own ID?

For example, say I have:

Table users: user_id, username

Table categories: category_id, category_name

Table users_categories: user_id, category_id

Would each row in "users_categories" need an additional ID field? What would the primary key of said table be? Thanks.

Upvotes: 1

Views: 2953

Answers (4)

Justin Gregoire
Justin Gregoire

Reputation: 2583

You could create a composite key that uses the both keys

Normally if there is no suitable key to be found in a table you want to create a either a composite key, made up of 2 or more fields,

ex:

Code below found here

  CREATE TABLE topic_replies (
topic_id int unsigned not null,
id int unsigned not null auto_increment,
user_id int unsigned not null,
message text not null,
PRIMARY KEY(topic_id, id));

therefor in your case you could add code that does the following:

ALTER TABLE users_categories ADD PRIMARY KEY (user_id, category_id);

therefor once you want to reference a certain field all you would need is to pass the two PKs from your other table, however to link them they need to each be coded as a foreign key.

ALTER TABLE users_categories ADD CONSTRAINT fk_1 FOREIGN KEY (category_id) REFERENCES categories (category_id);

but if you want to create a new primary key in your users_categories table that is an option. Just know that its not always neccessary.

Upvotes: 2

Tomalak
Tomalak

Reputation: 338118

If your users_categories table has a unique primary key over (user_id, category_id), then - no, not necessarily.

Only if you

  • want to refer to single rows of that table from someplace else easily
  • have more than one equal user_id, category_id combination

you could benefit from a separate ID field.

Upvotes: 1

Larry Lustig
Larry Lustig

Reputation: 50970

You have a choice. The primary key can be either:

  1. A new, otherwise meaningless INTEGER column.

  2. A key made up of both user_id and category_id.

I prefer the first solution but I think you'll find a majority of programmers here prefer the second.

Upvotes: 4

Scott
Scott

Reputation: 4200

Every table needs a primary key and unique ID in SQL no matter what. Just make it users_categories_id, you technically never have to use it but it has to be there.

Upvotes: 0

Related Questions