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:

        LeftValue int NOT NULL,
        RightValue int NOT NULL

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
        DECLARE @ret int
        set @ret = (SELECT COUNT(*) from Foo as t1, Foo as t2 where t1.LeftValue = t2.RightValue)
        RETURN @ret

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:

    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
          DECLARE @ret int
          SET @ret = (SELECT COUNT(*) from Foo as t1, Foo as t2 where t1.LeftValue = t2.RightValue)
          IF (@ret <> 0)
            RAISERROR ('Cross-field uniqueness has been violated.', 16, 1)

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:

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