Re: Remove implicit unique index creation on SERIAL

On Sun, 2002-08-18 at 02:35, 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?).

I don't like not overriding the value. A dataload example is a perfect
reason why you would want to. Anyway, this would require placing 2
triggers on the table in order to prevent changes of the value.

Personally I prefer the method that SAPdb uses. They make the column a
fixed() type (integer) and set the default. Nothing about NOT NULL or
UNIQUE.

Anyway, I think SERIAL is about assisting creation of a entry, not
enforcing it. Enforcement is trivial for those who don't mind the
additional overhead.