34

I'm curious if it is possible to create a table with a column that can never be changed, but the other columns of the table can.

For instance I could imagine a CreatedByUser column that should never be changed.

Is there a built-in functionality in SQL Server for this, or is it only possible via triggers or something else?

0

4 Answers 4

32

There is no built in declarative support for non updatable columns (except for specific predefined cases such as IDENTITY)

This Connect item requested it but was rejected. Add DRI to enforce immutable column values. See also Allow us to mark columns as immutable or const on the current feedback sit

An UPDATE trigger would probably be the most robust way of achieving this. It could check IF UPDATE(CreatedByUser) and raise an error and rollback the transaction if true.

1
  • I don't understand how that Azure feedback question doesn't have more votes...
    – jmoreno
    Commented Apr 18 at 12:00
15

I've made my implementation of the UPDATE TRIGGER approach suggested by Martin Smith's answer as follows:

CREATE TRIGGER trgAfterUpdateAsset ON dbo.Asset
FOR UPDATE AS
IF UPDATE(AssetTypeID) AND EXISTS (SELECT * FROM inserted i JOIN deleted d ON i.ID = d.ID WHERE i.AssetTypeID <> d.AssetTypeID)
BEGIN 
    RAISERROR ('AssetTypeID cannot change.', 16, 1);
    ROLLBACK TRAN
END     

(Note: The table has a Primary Key column, called ID).

I'm only rejecting the update if the value of AssetTypeID changes. So the column could be present in an update, and if the value did not change, than it would pass through. (I needed this way)

0
4

You could use a view with derived column. Try this

create table ro_test(id int primary key, CreatedByUser int)
go
create view v_ro_test
as
select id, CreatedByUser*1 CreatedByUser from ro_test
go

insert into ro_test values(1,10);
update ro_test
set CreatedByUser =11
where id =1;
select * from v_ro_test;
go
--ERROR--
update v_ro_test
set CreatedByUser =10
where id =1;

--BUT--
update v_ro_test
set id =2
where id =1;
select * from v_ro_test;
2
  • But you can just update the table and change it's values
    – Philipp M
    Commented Mar 10, 2013 at 10:06
  • 2
    @Philipp M But you can revoke access to the table and grant it to the view. Does not it?
    – msi77
    Commented Mar 10, 2013 at 20:48
-2

I know this is rather old, but there is a way to prevent a column from being UPDATED, which is the reason why SQL does not need an immutable schema, and is the same reason why Microsoft SQL Server did not ever fulfill the request linked by @Martin Smith in the answer.

The following line will prevent your table from being updated or altered anyhow.

GRANT INSERT ON database.immutable TO 'user'@'localhost' IDENTIFIED BY 'password';

You can grant every permission on every database and table, but keep "INSERT" only wherever you need a pseudo-immutable table. Hope this helps.

2
  • This doesn't answer the question. OP asked to prevent a single column from being updated. And you showed insert, not update. Commented Dec 30, 2019 at 15:14
  • This is not valid SQL Server syntax Commented Apr 18 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.