Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
I do not believe there is any way around this other than implementing triggers and only allowing create/update/delete statements through procedures. – Mark S. Rasmussen Mar 7 '13 at 11:37
@MartinSmith thanks, for the link. I guess that would be the answer to my question. So make it an answer and I'll accept it. – Philipp M Mar 7 '13 at 12:07
up vote 14 down vote accepted

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

This Connect item requested it but was rejected. Add DRI to enforce immutable column values

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.

share|improve this answer

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

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

(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)

share|improve this answer
If for a particular record the AssetTypeID is set to a non-null value and you run UPDATE Asset SET AssetTypeID = NULL WHERE Asset = the_id that there would be no rollback because WHERE i.AssetTypeID <> d.AssetTypeID in the trigger would evaluate to false, leaving that column editable. – Christiaan Westerbeek Mar 21 at 4:35

You could use a view with derived column. Try this

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

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

update v_ro_test
set id =2
where id =1;
select * from v_ro_test;
share|improve this answer
I'm not sure what you mean. Can you elaborate? – Philipp M Mar 8 '13 at 14:55
@PhilippM Defining the column as an expression in the view prevents it from being updated. – Paul White Mar 10 '13 at 4:03
But you can just update the table and change it's values – Philipp M Mar 10 '13 at 10:06
@Philipp M But you can revoke access to the table and grant it to the view. Does not it? – msi77 Mar 10 '13 at 20:48

Why are you updating the createdby column?

I would have two columns a [created_by] and a [modified_by] column, where the first insert would insert all the respective columns in the record, and any subsequent updates would just update the [modified_by] column ( via a trigger on in the application layer you can structure your update to only change the [modified_by] , along with your respective columns )

share|improve this answer
I think you've missed the point of the question. It was asking whether there was any built in support to enforce precisely that the column should not be updatable. – Martin Smith Mar 7 '13 at 15:21
@MartinSmith Exactly - we do have a modified column as well and I only was curious about whether an built in support for readonly exists – Philipp M Mar 7 '13 at 16:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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