id auto_inc column, but no primary key?-CodePudding

I apologize for the bad title, but I wasn't sure how else to phrase it.

Let's imagine for a moment that I wanted to create a us_states table as follow:

create table us_states
(
    id   serial,
    name varchar(256) not null constraint us_states_pk primary key,
    code varchar(256) not null
);

What tangible benefits, if any, are there to having an auto incremental id column in a db_table if I don't plan on leveraging it as the primary key for said db_table?

CodePudding user response：

There is zero value in adding an auto-incremented numerical column if it isn't a primary key or unique constraint.

If you have a unique column like name in your case, there are only two considerations not to use it as primary key:

storing a number instead of the name in tables that reference this one will save space
it is painful and should be avoided to modify primary key columns, so if the names change as part of the normal operation, it makes sense to use a different column as primary key