Constraint that would not accept a data if the value is true(1)

Question

How could I create a table that would not accept a row if there already exists a row with IsSuccess = 1 for that UpdateID?

Create table myTable
(
UpdateID int,
IsSuccess bit,
)

UpdateID   IsSuccess
--------   ---------
1          0      -- sucesss
1          0      -- success, There is no row for UpdateID = 1 where IsSuccess = 1
1          0      -- success, There is no row for UpdateID = 1 where IsSuccess = 1
1          1      -- success, There is no row for UpdateID = 1 where IsSuccess = 1
1          1      -- fails, There is row for UpdateID = 1 where IsSuccess = 1
1          0      -- fails, There is row for UpdateID = 1 where IsSuccess = 1
2          0      -- success, There is no row for UpdateID = 2 where IsSuccess = 1
2          1      -- success, There is no row for UpdateID = 2 where IsSuccess = 1
2          0      -- fails, There is row for UpdateID = 2 where IsSuccess = 1
2          1      -- fails, There is row for UpdateID = 2 where IsSuccess = 1

I have absolutely no clue why those two rows fail. Is it because there is a *previous* row where `IsSuccess = 1` for that `UpdateID`? — Aaron Bertrand, Sep 03 '14 at 01:31
Yes it will fails because there is a previous row where IsSuccess = 1 for that UpdateID — Karlx Swanovski, Sep 03 '14 at 01:39
Can you update your question to clarify please? Right now the wording isn't very clear. You might also show failure rows for more than one UpdateID. — Aaron Bertrand, Sep 03 '14 at 01:41
Now, how exactly did you determine the order of these rows? Another column you haven't told us about? — Aaron Bertrand, Sep 03 '14 at 03:27
Look at this http://stackoverflow.com/questions/18806361/add-unique-constraint-depending-on-value-in-sql — Abylay Sabirgaliyev, Sep 03 '14 at 06:05
@user99560 That doesn't quite solve the problem, since the unique index would still allow new rows with IsSuccess = 0. — Aaron Bertrand, Sep 03 '14 at 11:45
I see, i think you need check constraint with fuction or trigger. http://stackoverflow.com/questions/6612841/sql-server-check-constraint-failing-on-correct-input — Abylay Sabirgaliyev, Sep 03 '14 at 11:54
Trigger is better, since check constraints calling functions are unreliable at best. — Aaron Bertrand, Sep 03 '14 at 13:19
[Here's a link to a search result; the first few hits should scare you away from check constraints calling functions.](http://www.google.com/search?q=check+constraints+in+udfs+kuznetsov) — Aaron Bertrand, Sep 03 '14 at 19:24

Constraint that would not accept a data if the value is true(1)

0 Answers0