1

I'm confused as to how to assign primary keys.

For example, let's say I have these two tables:

users table where the user_id is unique:

+---------+----------+--------------+
| user_id | username |   password   |
+---------+----------+--------------+
|       1 | hello    | somepassword |
|       2 | world    | another      |
|       3 | stack    | overflow     |
+---------+----------+--------------+

posts table where the post_id is unique:

+---------+---------+--------------+
| post_id | user_id |   content    |
+---------+---------+--------------+
|       1 |       1 | Hello World! |
|       2 |       1 | Another.     |
|       3 |       3 | Number 3.    |
|       4 |       2 | Stack.       |
|       5 |       1 | Overflow.    |
+---------+---------+--------------+

Obviously for the users table the primary key should be user_id, but what should the primary key be in the posts table? post_id or user_id? Please explain.

Dharman
  • 30,962
  • 25
  • 85
  • 135
  • it is your wish.. but since user_id cannot be primary so you have no option other than making post_id as primary – N Kumar Nov 22 '14 at 07:05

3 Answers3

0

The primary key for the Posts table should also be the auto increment value, post_id, because it's the only thing that uniquely identifies each post, because each post has an id unlike any other. The user_id won't always be unique because the same user could have multiple posts (as far as I know) so it can't uniquely identify the posts. If you need to relate information between the tables you can always do a join on the user_id from both tables, however to identify things with a primary key, the post_id would be your best bet.

  • Okay, and about in this case that, let's for the sake of simplicity say that `user_id` in the **posts** table was also unique, but not auto incrementing. Which should the primary key be then? –  Nov 22 '14 at 07:23
  • Theoretically you could use either if they'll always be unique, but convention is probably still to use the post_id. But in reality, a single user should be able to do multiple posts probably, so the user_id just most likely will not be unique. –  Nov 22 '14 at 07:29
0

Surely, you have this sceneraio:

  • A user can post several posts.
  • A post can be posted, logically, by one user only.

Thus, you are dealing with a One-To-Many model.

Once these things are clear to you, you can guess that the primary key of users must appear as a foreign key in posts. This is what you obviously have done already.

Now, wether post_id is enough as the primary key of posts depends on the whole entity relationship model you have (how many other entities do you have and what are their relationship to each others).

However, you will not need, for this specific scenario to combine the foreign key user_id as a part of the primary key of posts.

Note: when you implement your tables, please add the constraints of auto_increment and not null to user_id and post_id.

Let's summerize all this mess in SQL:

Table users:

mysql> create table users (user_id int(2) unique  auto_increment not null, username varchar(15) not null, password varchar(20) not null, primary key(user_id));Query OK, 0 rows affected (0.33 sec)

Table posts:

mysql> create table posts(post_id int(2) unique auto_increment not null, user_id int(2) not null, content varchar(50) not null, foreign key(user_id) references users(user_id), primary key(post_id));
Query OK, 0 rows affected (0.26 sec)
-1

Of course it should be user_id because when you use ORM then it will map tables automatically from proper naming of keys You may refer ORM here : Good PHP ORM Library?

Community
  • 1
  • 1
Lalit Sharma
  • 555
  • 3
  • 12