When are check constraints evaluated?

Question

I have a table of Records

ID
EntityID 
Value
Status

and a table of Entities

ID
Col1
Col2
CurrentRecordID

CurrentRecordID should be the Record tied to the Entity with a Status of 0

I have two check constraints One on table Entity that checks to make sure that CurrentRecordID is in fact the current record and one on Record that checks to make sure that, if it has a status of 0, its EntityID has a CurrentRecordID that matches its ID.

So essentially, the two check constraints do the same thing, but separately on each table

Do check constraints run at the very end of a transaction or do they run like triggers after each insert/update on the tables?

If they run after each modification, will these two constraints conflict with one another (meaning, the constraint will throw an error before the next table has a chance to update its value).

Here is an example of a query that runs to insert a new record, and set it as the current for a given Entity

UPDATE Record SET Status = 1 WHERE Status = 0 AND EntityID = @EntityID
INSERT INTO Record(EntityID, Value, Status) VALUES(@EntityID, 100, 0)
DECLARE @RecordID INT = @@IDENTITY
UPDATE Entity SET CurrentRecordID = RecordID WHERE ID = @EntityID

Mineraloid

Constraints don't run like a trigger. They evaluate the rules prior to making a change to the data. I would also caution against using @@IDENTITY, you should instead use SCOPE_IDENTITY. If there is a trigger on the Record table that inserts into another table with identity you will receive that value, not the one you think. – Frauenfeld

@SeanLange So the flow would be Table 1 Change -> Check Constraint Table 1 -> Table 2 Change -> Check Constraint Table 2 Even if they are in the same transaction, Correct? Also, the query is just an example, I am actually using Entity Framework for this, but thanks anyways! – Mineraloid

That is correct. The constraint check happens when the data change is attempted. If the attempted change to Table1 fails the check constraint it will through an exception. – Frauenfeld

@SeanLange Great, thanks. I ended up using a computed column to solve my issue instead. If you want to make an answer with your solution, I will gladly accept it. – Mineraloid

Answer 1

Constraints don't run like a trigger. They evaluate the rules prior to making a change to the data.

The constraint check happens when the data change is attempted. If the attempted change to Table1 fails the check constraint it will through an exception.

Frauenfeld

allow me to add that on deletions checks aren't beign executed, so data changes would be narrowed to insertions and updates only, not deletions. – Nibbs

@Nibbs can you elaborate on what you mean? Constraints are evaluated for every DML operation. Foreign keys have to be checked. – Frauenfeld

if you look at my example below and execute, after running the script, for example the following statement delete from pepe where id = 1 you will see that CHECK isn't performed (as the RAISE INFO 'Displaying existing pepe records'; is not begin logged) – Nibbs

Well your example is in postgres but the question is about sql server. And not sure what the delete comment has to do with this at all. If the constraint is about legal values for the current table of course it wouldn't check that on delete. Why would it? It makes no sense to do that. – Frauenfeld

Alright Sean, i'm not saying that you are wrong... i was just pointing the case that on postgres the check constrains doesn't get executed at any kind of data change is attempted (those were your words) and a deletion could be seen as a change of data. You are right that the question is about sql server. I wonder if in sql server deletions trigger checks contrainst. As we both think, there will be no need of that. – Nibbs

No it does not check in sql server either. What I actually said is that is checks constraints for every DML which is correct. constraints might be check constraints or foreign key constraints. So on a delete it would have to evaluate any foreign key constraints that might get violated as a result of the delete. I guess I didn't understand the point of mentioning deletes but whatever. It produced a good discussion. :D – Frauenfeld

Same here Sean! Was very nice to have this discussion and to learn something new! Hope to cross path on other question xD – Nibbs

Answer 2

For completing the explanation given by Sean Lange, allow me to leave a ready-to-run example in order to see with your own eyes how a table check is performed prior to any data modifications.

CREATE TABLE pepe (
    id serial,
    state text NOT NULL
);
ALTER TABLE pepe ADD CONSTRAINT pepe_pk PRIMARY KEY (id);


CREATE OR REPLACE FUNCTION pepe_check_func() RETURNS boolean AS
$BODY$
  DECLARE
    temp_row record;
  BEGIN
    RAISE INFO 'Displaying existing pepe records';
    FOR temp_row IN
        SELECT id,state
        FROM pepe
    LOOP
        RAISE INFO '(id,state) = (%,%)',temp_row.id, temp_row.state;
    END LOOP;
    RETURN TRUE;
  END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;

ALTER TABLE pepe ADD CONSTRAINT pepe_check
CHECK (pepe_check_func());


insert into pepe (state) values ('go');

update pepe
set state = 'active';

select *
from pepe;

Nibbs

this example is in postgres, feel free to add another example for sql-server as well! – Nibbs