On 2016/11/01 1:01 PM, Simon Slavin wrote:
On 1 Nov 2016, at 10:45am, R Smith <rsm...@rsweb.co.za> wrote:
D - Horrible if you up some key value significantly and then update it back
down, because there is no way the Autoinc value should *EVER* be able/allowed
to come back down. It's a one-way street.
This is an additional argument for not allowing UPDATE to change an "INTEGER PRIMARY
KEY AUTOINCREMENT" value.
Well, either that, or an argument for not letting UPDATE dictate the
next incremental key value. While I think this argument carries equal
merit both ways (perhaps leaning more towards your suggestion), the
consequence of disallowing a column to be updated is too great a
disadvantage in my opinion (Even if it didn't break backwards
compatibility).
Which, according to GB, is what some other SQL engines do: attempts to change a
value in that column using UPDATE always generate an error. I didn't know
that. I looked it up. Apparently Microsoft's SQLSERVER blocks it, but I was
unable to find anything mentioning how any of the other big SQL engines handles
it.
Indeed, though MSSQL maintains what it calls "Identity" columns, which
(IIUC) is like Autoincrement Primary Key (but need not be the official
PK) that cannot be Updated. I don't know enough of it as I tend to avoid
it when using MSSQL. MySQL definitely allows updates to Autoinc columns,
and even updates the next key. Not sure about PostGres, but it is
clearly not a blanket assumption that can be made. Seems everybody has
their own take, which is ok, it just emphasizes what I tried to point
out previously - This AUTOINC thing is a luxury item, not a mandated
one, and one best avoided when you can roll your own.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
