Re: Remove implicit unique index creation on SERIAL columns?

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 know this causes problems for dumped and reloaded data. In MSSQL this
is gotten around by allowing the properties of the data type to be
altered, e.g. in MSSQL you can turn the IDENTITY property on or off (in
fact, I just checked and MSSQL won't allow you to turn on IDENTITY for a
column unless you turn off ALLOW NULLS). You can also specify an
exception to the rule when running BCP (the bulk loader command line
program).

The reason I think this behavior is good, is that it helps prevent toe
loss from stray bullets. E.g. you manually add a row where you've
specified some value that has not yet been reached by the sequence --
then when someday the sequence reaches said value, your insert fails on
a duplicate primary key insertion attempt.

If you really need to be able to insert or update a field with an
explicit value *sometimes* (and you really know what you're doing), then
use a plain sequence and a default, not a SERIAL.

Anyway, just my thoughts.

Joe