sql - Custom SERIAL / autoincrement per group of values

Question

I'm trying to make a blog system of sort and I ran into a slight problem.

Simply put, there's 3 columns in my article table:

id SERIAL,
category VARCHAR FK,
category_id INT

id column is obviously the PK and it is used as a global identifier for all articles.

category column is well .. category.

category_id is used as a UNIQUE ID within a category so currently there is a UNIQUE(category, category_id) constraint in place.

However, I also want for category_id to auto-increment.

I want it so that every time I execute a query like

INSERT INTO article(category) VALUES ('stackoverflow');

I want the category_id column to be automatically be filled according to the latest category_id of the 'stackoverflow' category.

Achieving this in my logic code is quite easy. I just select latest num and insert +1 of that but that involves two separate queries. I am looking for a SQL solution that can do all this in one query.

Answer 1

This has been asked many times and the general idea is bound to fail in a multi-user environment - and a blog system sounds like exactly such a case.

So the best answer is: Don't. Consider a different approach.

Drop the column category_id completely from your table - it does not store any information the other two columns (id, category) wouldn't store already.

Your id is a serial column and already auto-increments in a reliable fashion.

• Auto increment SQL function

If you need some kind of category_id without gaps per category, generate it on the fly with row_number():

• Serial numbers per group of rows for compound key