The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

MS SQL contraints and triggers

I'm using MS SQL Express 2005. I have a table defined like this:

    CREATE TABLE Foo (
        LeftValue int NOT NULL,
        RightValue int NOT NULL
    );
    CREATE UNIQUE CLUSTERED INDEX UK_Foo_LeftValue ON Foo (LeftValue);
    CREATE UNIQUE NONCLUSTERED INDEX UK_Foo_RightValue ON Foo (RightValue);

I want to guarantee that values are unique in the whole table, i.e. that no LeftValue equals any RightValue. I'm not allowed to define a self-join subquery in a CHECK contraint, so instead I define it as a function:

    CREATE FUNCTION Assert_Foo_LeftRight()
    RETURNS int
    AS
    BEGIN
        DECLARE @ret int
        set @ret = (SELECT COUNT(*) from Foo as t1, Foo as t2 where t1.LeftValue = t2.RightValue)
        RETURN @ret
    END

I then define a CHECK CONTRAINT which references this function:

    ALTER TABLE Foo ADD CONSTRAINT Check_Foo_LeftRight CHECK (dbo.Assert_Foo_LeftRight() = 0)

I insert some sample data into the table:

    INSERT INTO Foo (LeftValue, RightValue) VALUES (1, 10);
    INSERT INTO Foo (LeftValue, RightValue) VALUES (2, 3);
    INSERT INTO Foo (LeftValue, RightValue) VALUES (4, 7);
    INSERT INTO Foo (LeftValue, RightValue) VALUES (5, 6);
    INSERT INTO Foo (LeftValue, RightValue) VALUES (8, 9);

To test the constraint I then issue an update which tries to violate it:

    UPDATE Foo SET LeftValue = LeftValue - 1 where LeftValue = 8

As expected, this update fails:

    Msg 547, Level 16, State 0, Line 1
    The UPDATE statement conflicted with the CHECK constraint "Check_Foo_LeftRight". The conflict occurred in database "Test", table "dbo.Foo".
    The statement has been terminated.

However if I issue the following more complicated but equally illegal update, the update succeeds:

    UPDATE Foo SET
    LeftValue = CASE WHEN LeftValue BETWEEN 5 AND 6 THEN LeftValue +2 WHEN LeftValue BETWEEN 7 AND 8 THEN LeftValue -2 ELSE LeftValue END,
    RightValue = CASE WHEN RightValue BETWEEN 5 AND 6 THEN RightValue +2 WHEN RightValue BETWEEN 7 AND 8 THEN RightValue -2 ELSE RightValue END
    WHERE ((LeftValue >= 5) AND (LeftValue <= 8));

    (2 row(s) affected)

The contents of the table after this illegal update are as follows:

    1    10
    2    3
    4    7
    6    9
    7    8

This is illegal because the 7 value exists in both columns.

At this point the table is in an illegal state, and any statement, like "UPDATE Foo SET LeftValue = LeftValue", will fail the check contraint.

So I delete all records from the table, and try again with a trigger instead of with a check contraint:

    CREATE TRIGGER Trigger_Foo
    ON Foo
    FOR INSERT, UPDATE
    AS
          DECLARE @ret int
          SET @ret = (SELECT COUNT(*) from Foo as t1, Foo as t2 where t1.LeftValue = t2.RightValue)
          IF (@ret <> 0)
        BEGIN
            RAISERROR ('Cross-field uniqueness has been violated.', 16, 1)
        ROLLBACK TRANSACTION
        END

With this trigger in place, the illegal update is caught by the trigger:

    Msg 50000, Level 16, State 1, Procedure Trigger_Foo, Line 9
    Cross-field uniqueness has been violated.
    Msg 3609, Level 16, State 1, Line 2
    The transaction ended in the trigger. The batch has been aborted.

So I have two questions:
* Why doesn't the CHECK CONSTRAINT work properly, and is there a way to make it work?
* What disadvantages are there, if any, of using a trigger instead of a check contraint?
Christopher Wells Send private email
Friday, November 30, 2007
 
 
Triggers are slightly less performant than check constraints, but otherwise there is no problem with using triggers.

For an explanation on why your check constraint fails, read this blog entry: http://blogs.conchango.com/davidportas/archive/2007/02/19/Trouble-with-CHECK-Constraints.aspx

Friday, November 30, 2007
 
 
Probably has something to do with accessing the other rows. (ie doing a count on all the rows) The Check constrait is designed to work only on the one particular row that is being updated / inserted.  Updating one row at a time is OK, but when you update multiple rows at a time the updates from the other rows are not committed yet so the Check constraint passes.

The trigger however can "see" the data from other updates. So it works.

All just a guess...
DJ Send private email
Friday, November 30, 2007
 
 
Thank you for the reference to the blog entry: I understand this failure now.
Christopher Wells Send private email
Friday, November 30, 2007
 
 
How does the trigger solution work when two sessions both insert values that are supposed to be mutually exclusive, and both commit after the other has modifed the table? In general read consistency will ensure that neither session sees the other's changes. Doesn't sound robust to me.
David Aldridge Send private email
Sunday, December 02, 2007
 
 
I don't know how triggers works in general; but the trigger is being fired immediately after (or as part of) the UPDATE, before the transaction is committed.

I'm using the Serializable isolation level for this kind of update.
Christopher Wells Send private email
Monday, December 03, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz