CODEWITHSUNDEEP
sql-servercheck-constraints
MrZander
MrZander

Reputation: 3120

When are check constraints evaluated?

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

Upvotes: 7

Views: 3583

Answers (2)

Victor
Victor

Reputation: 3978

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;

Upvotes: -1

Sean Lange
Sean Lange

Reputation: 33580

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.

Upvotes: 5

Related Questions