Re: Remove implicit unique index creation on SERIAL columns?

On Mon, 19 Aug 2002, Bruce Momjian wrote:

> Joe Conway wrote:
> > Tom Lane wrote:
> > > Joe Conway <mail(at)joeconway(dot)com> writes:
> > >>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.
> > >
> > > Hmm, do you also want to eliminate the implicit NOT NULL constraint?
> > >
> > > I think that efficiency and orthogonality are adequate reasons for
> > > dissociating UNIQUE from SERIAL. The efficiency argument is pretty
> > > weak in the case of the NOT NULL part, though, so maybe backwards
> > > compatibility should win out there.
> >
> > To be honest I wasn't thinking about NOT NULL. I'd agree with leaving
> > that in place.
> >
> > Maybe I should restate my comment above: SERIAL should give me a column
> > that automatically increments -- no more, no less -- and it should not
> > allow me to override the value that it gives. Hence an implicit NOT
> > NULL, but also an implicit rejection of a manual insert/update of that
> > field (how hard would this be to do?).
>
> If don't understand. We already have a unique index on the SERIAL
> column, so why bother rejecting an insert/update that supplies the
> value? We need the column to be unique, and that is forced, but why
> prevent _any_ unique value from being used.

One reason is that the sequence won't respect those inserted values and
you'll get uniqueness errors on statements that don't give a value for the
column where you'd expect to be getting a working autogenerated value.