TransactionScope fails check constraint

Question

I am having a issue using TransactionScope and a check constraint in SQL Server.

I want to insert into the table as such:

Col A | Col B
------------
 Dave |  0
 Fred |  1

The table has a check constraint that there must always be an entry in Col B with '0'. The first row is inserting fine but the second row fails the constraint.

command.CommandText = @"INSERT INTO MyTable (ColA, ColB) VALUES(@ColA, @ColB)";                                   

            foreach (var row in model.Rows)
            {

                    command.Parameters["@ColA"].Value = model.ColA;
                    command.Parameters["@ColB"].Value = model.ColB;

                    command.ExecuteNonQuery();

            }

The check constraint calls the following function

IF EXISTS (SELECT * FROM mytable WHERE ColB = 0) RETURN 1

RETURN 0

Could this be because the constraint is only looking at committed data and if so how can it be told to look at uncommitted data as well

Without being able to see the definition for the constraint, it's not really possible to tell what's happening. — Damien_The_Unbeliever, Mar 30 '15 at 10:17
Why the `CHECK` calls a function for such a simple thing? The constraint should be defined simply as **colB=0**. — Alejandro, Mar 30 '15 at 10:24
A more interesting question could be why to even have ColB when it will always have the very same value of 0? Can it be removed altogether? — Alejandro, Mar 30 '15 at 10:28
@Alejandro I think the constraint is supposed to check all data, not the single row. — Maarten, Mar 30 '15 at 10:30
@DasDave Can you add the constraint definition to the question, and not just the function that the constraint calls? — Maarten, Mar 30 '15 at 10:31
@Maarten Sure, constraints always apply to every single row in the table. That's my point, if every single row must always have the very same value in ColB, why ColB has to exists to begin with? — Alejandro, Mar 30 '15 at 10:32
@Alejandro I do not know the reason for the constraint to exist, but the constraint function checks that there is a minimum of 1 row where the column B value is 0. Not every row, a single row. So the column B does have a reason the exist since the value 0 can shift between rows, as long as there is at least 1 where the value is 0. — Maarten, Mar 30 '15 at 10:39
Is the constraint meant to be "If there are any rows in this table, exactly one of them must have a colB value of 0" or "..., at least one of them must have a colB value of 0", or something else? — Damien_The_Unbeliever, Mar 30 '15 at 10:53
Obviously my example is simplified for not wanting to post production code. My overall questions is still can that function access the uncommitted data? — DasDave, Mar 30 '15 at 11:07

score 0 · Answer 1 · answered Mar 30 '15 at 10:48

I don't think Check Constraints are suitable for a scenario like yours.You should use a instead of update/insert trigger to check that there's at least one row (in the table and /or in inserted values)

You have a inserted table in a trigger that contains all the rows that will be inserted so you can write something like this :

IF NOT EXISTS (SELECT * FROM mytable a UNION inserted WHERE ColB = 0) RIASEERROR("At least one row with ColB=0 should exist")

TransactionScope fails check constraint

1 Answers1