CONSTRAINT to disable the update of some values
Carlos Sosa Albert arroba
I have a a table with data from some operations. The status (char(2)) of the
operations can be changed. But there are some status that are "final" and
should not be changed.
Is there a way the create a constraint that checks the previous status? So
if the previous is, for example, "fi" (final), won't allow an update?
Thanks!!!
--
____________________________
Carlos Sosa Albert
Tom Cooper
Tom
"Carlos Sosa Albert" <betun (arroba) hotmail> wrote in message
news:586FAC34-5831-412B...@microsoft.com...
--CELKO--
DRI for Transition Constraints
A transition constraint says that an entity can be updated only in
certain ways. These constraints are often modelled as a state
transition diagram. There is an initial state, flow lines that show
what are the next legal states, and one or more termination states.
As a very simple example, consider your marital life. Notice that we
have to start with Born and you are single. It is important to have
one initial state, but you can have many termination states. For
example, after you are born, you can die or get married, but you have
to be married to get a divorce. The state diagram is shown in on the
right.
In this example, we have only one termination state, Dead. Let’s
start with a table skeleton and try to be careful about the possible
states of our life:
CREATE TABLE Inventory
(product_id CHAR(15) NOT NULL PRIMARY KEY,
..);
CREATE TABLE Orders
(order_nbr CHAR(15) NOT NULL PRIMARY KEY,
product_id CHAR(15) NOT NULL
REFERENCES Inventory (product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);
We are being good programmers, using a DEFAULT and a CHECK()
constraint, but this does not prevent us from turning Born directly to
Dead, converting Divorced to Married, and so on. You can actually use
CHECK () constraints to enforce our state diagram, but you have to
store the current and previous states:
CREATE TABLE MyLife
(..
previous_state VARCHAR(10) NOT NULL,
current_state VARCHAR(10) DEFAULT 'Born' NOT NULL,
CHECK (CASE WHEN (previous_state = 'Born'
AND current_state IN ('Married', 'Born', 'Divorced'))
THEN 'T'
WHEN (previous_state = 'Married'
AND current_state IN ('Divorced', 'Dead'))
THEN 'T'
WHEN (previous_state = 'Divorced'
AND current_state IN ('Married', 'Dead'))
THEN 'T' ELSE 'F' END = 'T')
..);
In effect, the state diagram is converted into a search condition.
This procedure has advantages; it will pass information to the
optimizer, will port, and will usually run faster than procedural
code.
Another declarative way to enforce Transition Constraints is put the
state transitions into a separate table and then reference the legal
transitions. This requires that the target table have both the
previous, and the current, state in two columns. Using this example,
we would have something like this:
CREATE TABLE StateChanges
(previous_state VARCHAR(15) NOT NULL,
current_state VARCHAR(15) NOT NULL,
PRIMARY KEY (previous_state, current_state));
INSERT INTO StateChanges
VALUES ('Born', 'Born'), -- initial state
('Born', 'Married'),
('Born', 'Dead'),
('Married', 'Divorced'),
('Married', 'Dead'),
('Divorced', 'Married'),
('Divorced', 'Dead'),
('Dead', 'Dead'); -- terminal state
Carlos Sosa Albert arroba
--
____________________________
Carlos Sosa Albert
"--CELKO--" wrote:
> ...);
>
> In effect, the state diagram is converted into a search condition.
> This procedure has advantages; it will pass information to the
> optimizer, will port, and will usually run faster than procedural
> code.
>
> Another declarative way to enforce Transition Constraints is put the
> state transitions into a separate table and then reference the legal
> transitions. This requires that the target table have both the
> previous, and the current, state in two columns. Using this example,
> we would have something like this:
>
> CREATE TABLE StateChanges
> (previous_state VARCHAR(15) NOT NULL,
> current_state VARCHAR(15) NOT NULL,
> PRIMARY KEY (previous_state, current_state));
>
> INSERT INTO StateChanges
> VALUES ('Born', 'Born'), -- initial state
> ('Born', 'Married'),
> ('Born', 'Dead'),
> ('Married', 'Divorced'),
> ('Married', 'Dead'),
> ('Divorced', 'Married'),
> ('Divorced', 'Dead'),
> ('Dead', 'Dead'); -- terminal state
> .
>
Erland Sommarskog
> I have a a table with data from some operations. The status (char(2)) of
> the operations can be changed. But there are some status that are
> "final" and should not be changed.
>
> Is there a way the create a constraint that checks the previous status? So
> if the previous is, for example, "fi" (final), won't allow an update?
Connect item on this theme you can vote for:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=346200
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Tom Cooper
should have an exclamation point at the end
http://www.simple-talk.com/sql/t-sql-programming/constraint-yourself!/
But, if implemented, it helps, but does not prevent illegal changes - it
depends on the front end programmer or ad-hoc query writer always updating
the previous_state to the the prior value of current_state whenever
current_state changes. That is, you have to impose a rule like "don't ever
code Update MyLife Set current_state = <whatever>, instead always code
Update MyLife Set current_state = <whatever>, previous_state =
current_state".
That's certainly far better than handing the progammer a list of possible
changes nd depending on him/her to always implement that correctly. But,
unlike true DRI integrity, it does not prevent the user from incorrectly
changing the database, it just makes it less likely.
And I agree, Erland's connect suggestion would be a complete implementation
of this and this is a common problem and would like to see Microsoft offer
something like that.
Tom
"Carlos Sosa Albert" <betun (arroba) hotmail> wrote in message
news:65641C11-EF56-4778...@microsoft.com...
--CELKO--
Look again. I have to have a reference to a valid (previous_state,
current_state) pair to prevent someone from showing (dead, born) or
other silliness.
You then use the history table constaints to assure that all state
changes are properly ordered without gaps. This can enforce rules
about gettign marreid only aftr you are born and before you are dead.
Tony Rogerson
> VALUES ('Born', 'Born'), -- initial state
> ('Born', 'Married'),
> ('Born', 'Dead'),
> ('Married', 'Divorced'),
Why do you persist in this extremely bad practice of relying on the order of
columns in the table you are inserting into.
This is not just on forum posts but you've used the bad practice throughout
your books and published articles.
There is no ordering of columns in a table - you must ALWAYS name the
columns on the INSERT
INSERT INTO StateChanges (previous_state, current_state )
VALUES ('Born', 'Born'), -- initial state
('Born', 'Married'),
('Born', 'Dead'),
('Married', 'Divorced' );
You dare have a go at people on here when you haven't even got your own
house in order!
--ROGGIE--
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:e5d9cd2c-c5ac-43e2...@q2g2000yqq.googlegroups.com...
Tom Cooper
DIVORCED and it doesn't. For example
CREATE TABLE #MyLife
(
previous_state VARCHAR(10) NOT NULL,
current_state VARCHAR(10) DEFAULT 'Born' NOT NULL,
CHECK (CASE WHEN (previous_state = 'Born'
AND current_state IN ('Married', 'Born', 'Divorced'))
THEN 'T'
WHEN (previous_state = 'Married'
AND current_state IN ('Divorced', 'Dead'))
THEN 'T'
WHEN (previous_state = 'Divorced'
AND current_state IN ('Married', 'Dead'))
THEN 'T' ELSE 'F' END = 'T')
);
Insert #MyLife(previous_state, current_state) Values ('Born', 'Born');
Update #MyLife Set previous_state = current_state, current_state =
'Married';
Update #MyLife Set previous_state = current_state, current_state = 'Dead';
/* Now person is dead, so should stay that way, but nothing
prevents a programmer from coding the following
either in error or maliciously - say to collect that
persons pension */
Update #MyLife Set current_state = 'Divorced';
/* Cleanup */
Drop Table #Mylife;
Tom
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:245f6048-8ae8-4e18...@d8g2000yqf.googlegroups.com...
Andrew Morton
> But it's supposed to prevent you from changing (for example) DEAD to
> DIVORCED and it doesn't. For example
What if someone is erroneously entered as being dead? You'd have to put in a
flag to tell it you /really/ wanted to change their status back to alive.
"What do you mean I'm dead? Of course I'm alive, I'm here!"
"Computer says "no"."
--
Andrew
Erland Sommarskog
> What if someone is erroneously entered as being dead? You'd have to put
> in a flag to tell it you /really/ wanted to change their status back to
> alive.
>
> "What do you mean I'm dead? Of course I'm alive, I'm here!"
> "Computer says "no"."
That's good point, but then again those are only examples. Take a record
about a financial transaction. It has a couple of data like: account,
transaction type, booking day, value date etc. All of these may be wrong,
but if they are wrong, you don't change them, that's a big no-no. You
cancel the transaction and register a new one.