Re: IMMUTABLE columns in tables?

On Tuesday 15 June 2010 18.56:46 Craig Ringer wrote:

[IMMUTABLE columns]

> Column privs may be bypassed by a superuser

To be fair, constraints can be removed via ALTER TABLE, so this is not an
argument.

For me, the compelling reason to propose this is that it's much more
readable than either triggers or GRANTs. It may be more efficient at
runtime as well, but (i) this is just speculation and (ii) it has to be
weighed against the small cost even in cases it is not used, and the cost of
maintaining the additional code paths.

Chris:
> - What do other databases use as syntax for this?

Either my Google skills are lacking, or none of Oracle, MySQL, Sybase or MS
SQL does currently implement such a thing.

There is a proposal from 2008 for MS SQL:
http://connect.microsoft.com/SQLServer/feedback/details/346200
Whis is essentially identical to my proposal in its first part (IMMUTABLE as
a regular constraint) and adds an extension of the form "IMMUTABLE WHEN
condition" where condition is probably anything that would also be valid as
a check contstraint.

I think the latter part is probably too complicated and opens too many gray
areas (it's not IMMUTABLE if I just can UPDATE to invalidate condition,
change my "immutable" column, and update again to undo the first change...)

The only extension I think might be useful is IMMUTABLE WHEN NOT NULL, which
would allow the row to be unset on INSERT and later be frozen.

Another question opened in that MS SQL change request: should rows with
IMMUTABLE values be allowed to be deleted? Judgement call, personally, I
think that immutability and DELETE rights have nothing to do with each other
(I imagined use primarily to protect the "id" column against [accidential?]
modification.)

> - Does SQL standard have anything to say about how this sort
> of thing ought to be declared?

It appears that at least the SQL 2008 standard is not available for free.
If it were in SQL92 or whatever, I'd expect it to be already implemented,
but admitttedly I did not check these standards.

thanks for the feedback!
-- vbi

--
This is what open source software is all about: creating knockoffs and
giving them away, destroying the value of whatever the other guy is
selling.
-- Danyel Lyons, Forbes