Introduction<\/h2>\n\n\n\n
About two decades ago, I introduced the concept of transition constraints to show Data Validation in a database is a lot more complex than seeing if a string parameter really is an integer. In October of 2008, I did an article called Constraint Yourself<\/a>! on how to use DDL constraints to assure data integrity. One of the topics in that piece was a look at state transition constraints via an auxiliary table.<\/p>\n\n\n\n Let me give you some introduction, and for some of you flashbacks to your early computer science classes. There is an initial state, flow lines that show what are the next legal states, and one or more termination states. These diagrams also called finite state automata, and you\u2019ll see it in freshman computer science classes. To date, we might implement them with a graph database. What is worth relearning are our old tricks from SQL.<\/p>\n\n\n\n This state transition diagram was deliberately simplified, but it is good enough to explain principles. To keep the discussion as simple as possible, my table is for only one person\u2019s marital status over his life. Here is a skeleton DDL with the needed What is not shown on it are which An aspect of this problem that I did not consider in the first article is the time dimension. We want to see a temporal path from an initial state to a terminal state. State changes do not happen all at once, but are spread over time. An acorn becomes an oak tree before it becomes lumber and finally my chest of drawers. The acorn does not jump immediately to being a chest of drawers.<\/p>\n\n\n\n Some of the changes are controlled by time. For example, one cannot get married immediately after being born, they have to wait to be of legal age. A business offer can expire in a set number of days. I am sure you can think of many additional examples as well.<\/p>\n\n\n\n For a production system, you would need a more complete set of temporal columns to guarantee that we have no gaps in the history, but this will do for now. We now need a stored procedure to add data to the The first block of code locates the most recent state of my life, based on the date. The second block of code will insert an initial state if the table is empty. This is a safety feature but there probably ought to be a separate procedure to create the set of initial states. The new state has to be an actual change, so there is a block of code to be sure. The changes must move forward in time. Finally, we build a row using the most recent state as the new previous state, the input change state and the date. If the state change is illegal, the If you had other business rules, you could also add them to the code in the same way. You should have noticed that if someone makes changes directly to the This poor person popped into existence without being properly born , committed bigamy and died twice. And you think your life is tough! Here is a simple validation procedure to catch those errors.<\/p>\n\n\n\n The CTE numbers the steps of the temporal path from an initial node to a middle or terminal node. This chain has to be unbroken which means going from step (n) to step(n+1) has to be a legal change in the A little note on the programming technique used. The union of separate queries to do one validation at a time can often be made faster by combining some of the queries. But there are trade-offs; this code is easy to read and to maintain and (hopefully) it will not be run often. It is also hard to get error messages from a single statement. Look back at the This is not easy to read or to get error messages that tell you if the What is still missing is the temporal aspect of state changes. In this example, the To make up some data, let\u2019s assume that the age of consent is 18 (12 months * 18 years = 216), that you have to wait 3 months into your marriage before getting a divorce, and that you must be divorced 2 months before you can re-marry. Of course, you can die instantly.<\/p>\n\n\n\n First I will clear the data from the table, add a column for the required months between actions, and then reload the table.<\/p>\n\n\n\n The first question is where to check for temporal violations; during insertion or with validation procedures? My answer is both. Whenever possible, do not knowingly put bad data into a schema so this should be done in the Here is a query which will tell you what state change in the chain has an improper duration and what the disagreement is.<\/p>\n\n\n\n Inserting a new life change is not a simple matter of putting a (previous_state, current_state, start_date) row into the table. To do it right, you can put conditions into the A slightly different model will keep a ( Here is some skeleton DDL for a bid application to explain this better.<\/p>\n\n\n\n The DDL has a bid number and the The next DDL will extend the state change table to be a bit more flexible (if you wish to test this out, the process to recreate this would be the same as in the previous section).<\/p>\n\n\n\n The DDL for the state changes gets a new column to tell us if the duration is optional or mandatory. The insertion procedure is a bit trickier. The All it needs is the bid number and what state you want to use. If you don\u2019t give me a previous state, I assume that this is an initial row and repeat the current state you just gave me. If you don\u2019t give me a start date, I assume you want the current date. If you don\u2019t give me an expiration date, I construct one from the There are other tricks to assure that there are no gaps between the rows using DDL, but that is another article for another day.<\/p>\n\n\n\n Create an auxiliary StateChanges table with (previous_state, current_state) as its primary key, listing all valid transitions. In the entity table, add a FOREIGN KEY (previous_state, current_state) REFERENCES StateChanges (previous_state, current_state). Any INSERT or UPDATE that specifies an invalid transition will fail with a FK violation. The state diagram (initial states, valid transitions, terminal states) maps directly to rows in the StateChanges table.<\/p>\n <\/div>\n Add start_date and expiry_date columns to the entity table. Each row represents a state with a time range. Use CHECK constraints or a stored procedure to ensure: no gaps between consecutive states (the next start_date equals the previous expiry_date), no overlapping states, and chronological ordering. Combine with SQL Server temporal tables<\/a> (SYSTEM_VERSIONING) for automatic history tracking of all state changes.<\/p>\n <\/div>\n The companion article (Constraint Yourself!<\/a>) covers the basic pattern: the StateChanges auxiliary table, the FOREIGN KEY constraint, and simple examples. This article extends that with temporal attributes (state duration, expiry tracking), CTE-based path validation (verifying the entire chain of transitions from initial state), and stored procedure<\/a> wrappers for controlled state updates. Read the introduction first if you are new to the pattern.<\/p>\n <\/div>\n <\/section>\n","protected":false},"excerpt":{"rendered":" Implement state transition constraints in SQL Server using FOREIGN KEY references, temporal attributes, CTE path validation, and stored procedure wrappers. Advanced techniques for enforcing valid state changes.…<\/p>\n","protected":false},"author":96214,"featured_media":106449,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[53,143531,143539],"tags":[159072],"coauthors":[6781],"class_list":["post-106446","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-t-sql-programming-sql-server","category-theory-and-design","tag-database-theory"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106446","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/96214"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=106446"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106446\/revisions"}],"predecessor-version":[{"id":109268,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106446\/revisions\/109268"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/106449"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=106446"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=106446"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=106446"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=106446"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"1157-BornMarriedDivorcedDead.jpg\"\/](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/04\/1157-bornmarrieddivorceddead-jpg.jpeg\")
<\/figure>\n\n\n\nFOREIGN KEY<\/code> reference to valid state changes and the date that the current state started.<\/p>\n\n\n\nCREATE TABLE MyLife (\n previous_state VARCHAR(10) NOT NULL,\n current_state VARCHAR(10) NOT NULL,\n CONSTRAINT Improper_State_Change\n FOREIGN KEY (previous_state, current_state)\n REFERENCES StateChanges (previous_state, current_state),\n start_date DATE NOT NULL PRIMARY KEY,\n --etc.\n);<\/pre><\/div>\n\n\n\n
nodes<\/code> are initial states (in this case \u201cBorn\u201d) and which are terminal or final states (in this case \u201cDead\u201d, a very terminal state of being). A terminal node can be the current state of a middle node, but not a prior state. Likewise, an initial node can be the prior state of a middle node, but not the current state. I did not write any CHECK()<\/code> constraints<\/a> for those conditions. It is easy enough to write a quick query with an EXISTS()<\/code> predicate to do this and I will leave that as an exercise for the reader. Let\u2019s load the diagram into an auxiliary table with some more constraints.<\/p>\n\n\n\nCREATE TABLE StateChanges (previous_state VARCHAR(10) NOT NULL,\n current_state VARCHAR(10) NOT NULL,\n PRIMARY KEY (previous_state, current_state),\n state_type CHAR(1) DEFAULT 'M' NOT NULL\n CHECK (state_type IN ('I', 'T', 'M')), \/*initial, terminal, middle*\/\n CONSTRAINT Node_type_violations\n CHECK (CASE WHEN state_type IN ('I', 'T')\n AND previous_state = current_state\n THEN 'T'\n WHEN state_type = 'M'\n AND previous_state <> current_state\n THEN 'T' ELSE 'F' END = 'T')\n);\n\n\nINSERT INTO StateChanges (previous_state, current_state, state_type)\n VALUES ('Born', 'Born', 'I'), -- initial state\n ('Born', 'Married', 'M'),\n ('Born', 'Dead', 'M'),\n ('Married', 'Divorced', 'M'),\n ('Married', 'Dead', 'M'),\n ('Divorced', 'Married', 'M'),\n ('Divorced', 'Dead', 'M'),\n ('Dead', 'Dead', 'T'); -- terminal state<\/pre><\/div>\n\n\n\nThe Time Dimension<\/h2>\n\n\n\n
MyLife<\/code> table. Here is one solution which is deliberately broken into clear steps for clarity.<\/p>\n\n\n\nCREATE PROCEDURE Change_State\n(@in_change_date DATE,\n @in_change_state VARCHAR(10))\nAS\nBEGIN\nDECLARE @most_recent_state VARCHAR(10);\nSET @most_recent_state\n = (SELECT current_state\n FROM MyLife\n WHERE start_date\n = (SELECT MAX(start_date) FROM MyLife));\n\n\/* insert initial state if empty *\/\nIF NOT EXISTS (SELECT * FROM MyLife)\n AND @in_change_state\n IN (SELECT previous_state\n FROM StateChanges\n WHERE state_type = 'I')\nBEGIN\nINSERT INTO MyLife (previous_state, current_state, start_date)\nVALUES (@in_change_state, @in_change_state, @in_change_date);\nRETURN;\nEND;\n\n\/* must be a real state change *\/\nIF @in_change_state = @most_recent_state\nBEGIN\nRAISERROR ('This does not change the state.', 16, 1);\nRETURN;\nEND;\n\n\/* must move forward in time *\/\nIF @in_change_date <= (SELECT MAX(start_date) FROM MyLife)\nBEGIN\nRAISERROR ('Violates time sequence.', 16, 1);\nRETURN;\nEND;\n\nINSERT INTO MyLife (previous_state, current_state, start_date)\nVALUES (@most_recent_state, @in_change_state, @in_change_date);\nEND;<\/pre><\/div>\n\n\n\nFOREIGN KEY<\/code> is violated, and we get an error.<\/p>\n\n\n\nMyLife<\/code> Table, they are pretty much free to screw the data. It is a good ideas to have a procedure that checks to see that MyLife is in order. Let\u2019s load the table with bad data:<\/p>\n\n\n\nINSERT INTO MyLife (previous_state, current_state, start_date)\nVALUES ('Born', 'Married', '1990-09-05'),\n ('Married', 'Divorced', '1999-09-05'),\n ('Married', 'Dead', '2010-09-05'),\n ('Dead', 'Dead', '2011-05-10'),\n ('Dead', 'Dead', '2012-05-10');<\/pre><\/div>\n\n\n\nCREATE PROCEDURE ValidateLife\nAS\nWITH Sequenced_State_History\nAS\n(SELECT previous_state, current_state,\n ROW_NUMBER() OVER (ORDER BY start_date)\n AS change_seq\n FROM MyLife)\n\n\/* There is chain of links from the initial state to the current state *\/\nSELECT 'Missing link(s) in History'\n FROM Sequenced_State_History AS H1, Sequenced_State_History AS H2\n WHERE H1.change_seq + 1 = H2.change_seq\n AND H1.current_state <> H2.previous_state\n\nUNION ALL\n\n\/* has one and only one initial state *\/\nSELECT 'No unique initial state.'\n FROM MyLife AS M, StateChanges AS C\n WHERE C.state_type = 'I'\n AND M.previous_state = C.previous_state\n AND M.current_state = C.previous_state\nHAVING COUNT(*) <> 1\n\nUNION ALL\n\n\/* has zero or one terminal state *\/\nSELECT 'Too many terminal states.'\n FROM MyLife AS M, StateChanges AS C\n WHERE C.state_type = 'T'\n AND M.previous_state = C.previous_state\n AND M.current_state = C.previous_state\nHAVING COUNT(*) > 1;<\/pre><\/div>\n\n\n\n
StateChanges<\/code> table. This chain can have only one initial node, so let\u2019s check for that next. Finally, the chain is either still in progress or it has reached a single terminal node.<\/p>\n\n\n\nChangeState()<\/code> procedure; the two IF<\/code> and RAISERROR()<\/code> blocks of code could have been converted into CASE<\/code> expressions that will generate NULL<\/code>s, folded into the INSERT INTO<\/code> statement and cause the insertion to fail.<\/p>\n\n\n\nINSERT INTO MyLife (previous_state, current_state, start_date)\nVALUES\n(NULLIF (@in_change_state, @most_recent_state),\n @in_change_state,\n CASE WHEN @in_change_date\n <= (SELECT MAX(start_date) FROM MyLife)\n THEN NULL ELSE @in_change_date END);<\/pre><\/div>\n\n\n\n
@in_change_date<\/code> is invalid in that it violates the time sequence.<\/p>\n\n\n\nThe Temporal Side of Changes<\/h2>\n\n\n\n
(Born\u2019, \u2018Married\u2019)<\/code> change would have to deal with the minimum age of consent. The (Married\u2019, \u2018Divorced\u2019)<\/code> change often has a legal waiting period. While technically a business rule, you know that no human being has lived over 150 years, so a gap that size is a data error. The terminal and initial states are instantaneous, however. Let\u2019s add more flesh to the skeleton table:<\/p>\n\n\n\nCREATE TABLE StateChanges\n(previous_state VARCHAR(10) NOT NULL,\n current_state VARCHAR(10) NOT NULL,\n PRIMARY KEY (previous_state, current_state),\n state_type CHAR(1) DEFAULT 'M' NOT NULL\n CHECK (state_type IN ('I', 'T', 'M')), \/*initial, terminal, middle*\/\n state_duration INTEGER NOT NULL -- unit of measure is months\n CHECK (state_duration >= 0),\n CONSTRAINT Node_type_violations\n CHECK (CASE WHEN state_type IN ('I', 'T')\n AND previous_state = current_state\n THEN 'T'\n WHEN state_type = 'M'\n AND previous_state <> current_state\n THEN 'T' ELSE 'F' END = 'T')\n);<\/pre><\/div>\n\n\n\n--drop constraint that references StateChanges\nALTER TABLE MyLife DROP CONSTRAINT Improper_State_Change;\n\n--clear out domain data\nDELETE FROM StateChanges;\n\n--add the new column for the state duration requirements\nALTER TABLE StateChanges ADD state_duration INTEGER NOT NULL;\n\n--reload table\nINSERT INTO StateChanges (previous_state, current_state,\n state_type,state_duration)\n VALUES ('Born', 'Born', 'I', 0), -- initial state\n ('Born', 'Married', 'M', 216),\n ('Born', 'Dead', 'M', 0),\n ('Married', 'Divorced', 'M', 3),\n ('Married', 'Dead', 'M', 0),\n ('Divorced', 'Married', 'M', 2),\n ('Divorced', 'Dead', 'M', 0),\n ('Dead', 'Dead', 'T', 0); -- terminal state\n\n--add back the constraint\nALTER TABLE MyLife\nADD CONSTRAINT Improper_State_Change\n FOREIGN KEY (previous_state, current_state)\n REFERENCES StateChanges (previous_state, current_state)<\/pre><\/div>\n\n\n\nChangeState()<\/code> procedure. But someone or something will subvert the schema and you have to be able to find and repair the damage.<\/p>\n\n\n\nWITH Sequenced_State_History\nAS\n(SELECT previous_state, current_state, start_date,\n ROW_NUMBER() OVER (ORDER BY start_date) AS change_seq\n FROM MyLife)\n\n\/* There is chain of links from the initial state to the current state *\/\nSELECT H2.change_seq, H2.previous_state, H2.current_state,\n DATEDIFF (MM, H1.start_date, H2.start_date) AS actual_state_duration,\n C.state_duration AS expected_state_duration\n FROM Sequenced_State_History AS H1,\n Sequenced_State_History AS H2,\n StateChanges AS C\n WHERE H1.change_seq + 1 = H2.change_seq\n AND DATEDIFF (MM, H1.start_date, H2.start_date) <= C.state_duration\n AND C.previous_state = H2.previous_state\n AND C.current_state = H2.current_state;<\/pre><\/div>\n\n\n\n
INSERT INTO<\/code> statement to cause errors when there is bad data.<\/p>\n\n\n\nCREATE PROCEDURE Life_Status_Change\n(@in_change_state VARCHAR(10),\n @in_most_recent_state VARCHAR(10),\n @in_change_date DATE)\nAS\nINSERT INTO MyLife (previous_state, current_state, start_date)\nVALUES\n(NULLIF (@in_change_state, @in_most_recent_state),\n @in_change_state,\n CASE WHEN @in_change_date\n <= (SELECT MAX(start_date) FROM MyLife)\n THEN NULL ELSE @in_change_date END);\n<\/pre><\/div>\n\n\n\n
start_date, expiry_date<\/code>) pair in the history table. In the case of the MyLife example, the durations were the minimum values for certain changes. But a lot of commercial situations have a fixed lifespan. Warranties, commercial offers and bids expire in a known number of days. This means adding another column to the StateChanges<\/code> table that tells the insertion program if the expiration date is optional (shown with a NULL<\/code>) or mandatory (computed from the duration).<\/p>\n\n\n\nCREATE TABLE MyBids\n(bid_nbr INTEGER NOT NULL,\n previous_state VARCHAR(10) NOT NULL,\n current_state VARCHAR(10) NOT NULL,\n CONSTRAINT Improper_State_Change\n FOREIGN KEY (previous_state, current_state)\n REFERENCES StateChanges (previous_state, current_state),\n start_date DATE NOT NULL,\n expiry_date DATE, -- null means still open.\n CHECK (start_date <= expiry_date),\n PRIMARY KEY (bid_nbr, start_date),\n --etc.\n);<\/pre><\/div>\n\n\n\n
start_date<\/code> as the primary key and a new column for the expiration date. Obviously, the bid has to exist for a while, so add a constraint to keep the date order right.<\/p>\n\n\n\nCREATE TABLE StateChanges\n(previous_state VARCHAR(10) NOT NULL,\n current_state VARCHAR(10) NOT NULL,\n PRIMARY KEY (previous_state, current_state),\n state_duration INTEGER NOT NULL,\n duration_type CHAR(1) DEFAULT 'O' NOT NULL\n CHECK ('O', 'M')), -- optional, mandatory\n - etc.\n);<\/pre><\/div>\n\n\n\nVALUES<\/code> clause has more power than most programmers use. The list can be more than just literal values or simple scalar variables. But using CASE<\/code> expressions lets you avoid if-then-else procedural logic in the procedure body.<\/p>\n\n\n\nStateChanges<\/code> table with a scalar subquery. Here is the skeleton DDL for an insertion procedure.<\/p>\n\n\n\nCREATE PROCEDURE Bid_Status_Change\n(@in_bid_nbr INTEGER,\n @in_previous_state VARCHAR(10),\n @in_current_state VARCHAR(10),\n @in_start_date DATE,\n @in_expiry_date DATE)\nAS\nINSERT INTO MyBids (bid_nbr, previous_state, current_state, start_date, expiry_date)\nVALUES (@in_bid_nbr, -- required\n COALESCE (@in_previous_state, @in_current_state),\n @in_current_state, -- required\n COALESCE (@in_start_date, CAST (CURRENT_TIMESTAMP AS DATE),\n (SELECT COALESCE (@in_expiry_date,\n DATEADD(MM, @in_start_date, S.state_duration))\n FROM StateChanges AS S\n WHERE S.previous_state =\n COALESCE (@in_previous_state, @in_current_state)\n AND S.current_state = @in_current_state\n AND S.duration_type = 'M'))\n );<\/pre><\/div>\n\n\n\n
Fast, reliable and consistent SQL Server development…<\/h3>\n
FAQs: State transition constraints in SQL Server<\/h2>\n\n
1. How do you enforce state transitions in SQL Server?<\/h3>\n
2. How do you add temporal tracking to state transitions?<\/h3>\n
3. What is the difference between this article and the introduction to state transitions?<\/h3>\n