Add DRI to enforce immutable column values - by Erland Sommarskog

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


38
0
Sign in
to vote
ID 346200 Comments
Status Closed Workarounds
Type Suggestion Repros 1
Opened 5/26/2008 12:52:35 PM
Access Restriction Public

Description

It's not an uncommon business requirement that once data has been entered into the
database. It could be as harsh as once written, never change. This would be true for an 
audit table for instance. It could also be that the data is permitted to change in some
initial phase, but after a certain event data is unchangeable. For instance, once an
order has been shipped, it cannot be changed.

Such constraints can be enforced with triggers, but writing triggers is a fairly complex
and tedious affair. Since these sort of requirements are quite common, it would 
desirable to express in a declarative form. Actually, when I think of the system I 
work with, we probably have a whole bunch of columns that we cannot permit
to change, but we don't enforce this in triggers. We just naïvely assume that
it doesn't happen.
Sign in to post a comment.
Posted by Microsoft on 3/10/2011 at 6:08 PM
Hello Erland,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to reopen it and we will take another look.

--
Umachandar, SQL Programmability Team
Posted by Jason Kresowaty on 8/31/2008 at 5:08 AM
This is a great suggestion. I am pretty sure that whether or not rows with IMMUTABLE constraints can be deleted is something that the user would need to be able to configure. The simplest way to do this would be to create a DELETE trigger to stop the operation if the user does not want the row to be deleted.

And, although it might be obvious, it should be possible to ENABLE and DISABLE an IMMUTABLE constraint with sufficient priviledges, just like the other kinds of constraints.
Posted by Microsoft on 5/27/2008 at 4:18 PM
Hi Erland,
Thanks for your suggestion. We will look at this for a future version of SQL Server.

Thanks
Umachandar, SQL Programmability Team
Posted by MatthewRoche on 5/26/2008 at 4:02 PM
I think this is an excellent idea and would make SQL Server much more manageable. The more that we can do to use declarative semantics to enforce data integrity, the better.