Re: Remove implicit unique index creation on SERIAL columns?

Hi

-*- Joe Conway <mail(at)joeconway(dot)com> [ 2002-08-18 06:36 ]:
> 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.

I like the functionality you are suggesting. However, when I started thinking about what size this field should be, the idea of keeping the current SERIAL "data type" and having a SERIAL flag (CREATE TABLE foo (id int serial))... so it's pretty much the same as a seqence, but enforced so that you can't tamper with it. The best part about this is obviously that it doesn't change behaviour of the old SERIAL.

Am I right about this, or am I missing something here?

--
Regards,
Tolli
tolli(at)tol(dot)li