Re: Remove implicit unique index creation on SERIAL columns?

Rod Taylor wrote:
> I'd like to propose dropping the auto-creation of UNIQUE indexes on
> serial columns for the following reasons:
> 
> 1. Serials with indexes are quite difficult to handle in pg_dump.  It
> means that the implicitly created unique index must be destroyed prior
> to loading the data, then re-created afterward else risk a performance
> hit.
> 
> 
> 2. SERIAL columns are usually used as the primary key of the table.  As
> such one must specify PRIMARY KEY at creation time to override the
> implicitly created UNIQUE index.
> 
> 3. Consistency with other databases.
> 
> MySQL's AUTO_INCREMENT suggests quite heavily that an index be applied,
> but it doesn't appear to do it for you.
> 
> Oracle has SEQUENCES, but it is up to the user to associate them with a
> column as per my understanding.  At least, thats all I could find.
> 
> SAP -> SERIAL and SERIAL(n).  No index is created (creates sequence /
> default)

FWIW, also MSSQL.

I agree 100%. If you want an index, unique constraint, or primary key on 
a SERIAL, I think you should explicitly add it. SERIAL should give me a 
column that automatically increments -- no more, no less.

Joe