Re: Remove implicit unique index creation on SERIAL columns?

Hi

-*- Joe Conway <mail@joeconway.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,
theidea of keeping the current SERIAL "data type" and having a SERIAL flag (CREATE TABLE foo (id int serial))... so
it'spretty much the same as a seqence, but enforced so that you can't tamper with it.  The best part about this is
obviouslythat it doesn't change behaviour of the old SERIAL.
 

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

-- 
Regards,
Tolli
tolli@tol.li