CODEWITHSUNDEEP
mysql
Ali
Ali

Reputation: 267257

Adding an auto incrementing column to MySQL database which isn't the primary key

I have a table called foo with columns like the following:

id 
usedId (id of the person who posted this foo)
title
postedDate

What I want to do, is add another column called position, which will record how many foos a user has posted. E.g, his first posted foo will have the position of 1, 2nd will have 2, 100th will have 100, and so on. But this position is specific to the userId only.

Is there a way to add such a column to mysql, or I must calculate the position programatically?

Upvotes: 1

Views: 133

Answers (3)

Shane
Shane

Reputation: 1075

Calculating it pragmatically is the best way to keep your database normalised, since you shouldn't have any data stored which can be calculated later. You should just order by the postedDate, and count which position you're on with each iteration.

However, if you were going to go ahead with this, you'd have to count the number of rows that the user already has, using a sub-query. So this would be part of your insert query:

[..], position = (SELECT COUNT(*) + 1 FROM foo WHERE userId = $userId), [..]

The sub-query would just return the number of rows already in the table for the user with the ID $userId, and increment it by one.

Upvotes: 2

Ruslan Polutsygan
Ruslan Polutsygan

Reputation: 4461

It seems like your question is related to

mysql auto-increment on a non-primary key

Or you should use triggers

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125454

insert into foo (id, usedId, title, postedDate, position)
values (
1, 2, 'title', '2011-07-23', (select count(*) + 1 from foo where usedId = 2)
)

Upvotes: 1

Related Questions