A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
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()
DECLARE @ret int
set @ret = (SELECT COUNT(*) from Foo as t1, Foo as t2 where t1.LeftValue = t2.RightValue)
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:
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
FOR INSERT, UPDATE
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?
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...
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.
Sunday, December 02, 2007
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz