Checking sql unique value with constraint

If the table can have duplicate ID values, then a check constraint wouldn't work for your situation. I think the only way would be to use a trigger. If you are looking for an example then I can post one. But in summary, use a trigger to test if the inserted/updated ID has a status of 1 that is duplicated across the same ID.

EDIT: You could always use a unique constraint on ID and Value. I'm thinking that will give you what you are looking for.

You could put this into an insert/ update trigger to check to make sure only one combination exists with the 1 value; if your condition is not met, you could throw a trappable error and force the operation to roll back.

If you can use NULL instead of 0 for a zero-status, then you can use a UNIQUE constraint on the pair and it should work. Since NULL is not an actual value (NULL != NULL), then rows with multiple nulls should not conflict.

IMHO, this basically is a normalisation problem. The column named "id" does not uniquely address a row, so it can never be a PK. At least a new (surrogate) key(element) is needed. The constraint itself cannot be expressed as an expression "within the row", so it has to be expressed in terms of a FK.

So it breaks down into two tables: One with PK=id, and a FK REFERENCING two.sid

Two with PK= surrogate key, and FK id REFERENCING one.id The original payload "value" also lives here.

The "one bit variable" disappears, because it can be expressed in terms of EXISTS. (effectively table one points to the row that holds the token)

[I expect the Postgres rule system could be used to use the above two-tables-model to emulate the intended behaviour of the OP. But that would be an ugly hack...]

EDIT/UPDATE:

Postgres supports partial/conditional indices. (don't know about ms-sql)

DROP TABLE tmp.one;
CREATE TABLE tmp.one
    ( sid INTEGER NOT NULL PRIMARY KEY -- surrogate key
    , id INTEGER NOT NULL
    , status INTEGER NOT NULL DEFAULT '0'
    /* ... payload */
    );
INSERT INTO tmp.one(sid,id,status) VALUES
  (1,1,0) , (2,1,1) , (3,1,0)
, (4,2,0) , (5,2,0) , (6,2,1)
, (7,3,0) , (8,3,0) , (9,3,1)
  ;

CREATE UNIQUE INDEX only_one_non_zero ON tmp.one (id)
    WHERE status > 0 -- "partial index" 
    ;

\echo this should succeed
BEGIN ;
UPDATE tmp.one SET status = 0 WHERE sid=2;
UPDATE tmp.one SET status = 1 WHERE sid=1;
COMMIT;

\echo this should fail
BEGIN ;
UPDATE tmp.one SET status = 1 WHERE sid=4;
UPDATE tmp.one SET status = 0 WHERE sid=9;
COMMIT;

SELECT * FROM tmp.one ORDER BY sid;

I came up with a solution

First create a function

CREATE FUNCTION [dbo].[Check_Status] (@ID int)
RETURNS INT
AS
BEGIN
 DECLARE @r INT;
 SET @r =
  (SELECT SUM(status) FROM dbo.table where ID= @ID);
 RETURN @r;
END

Second create a constraint in table

([dbo].[Check_Status]([ID])<(2))

In this way one ID could have single status (1) and as many as possible status (0).

create function dbo.IsValueUnique
(
     @proposedValue varchar(50)
    ,@currentId int
)
RETURNS bit
AS
/*
--EXAMPLE
print dbo.IsValueUnique() -- fail
print dbo.IsValueUnique(null) -- fail
print dbo.IsValueUnique(null,1) -- pass
print dbo.IsValueUnique('Friendly',1) -- pass
*/
BEGIN
    DECLARE @count  bit

    set @count =
    (
        select      count(1)
        from        dbo.MyTable
        where       @proposedValue is not null
        and         dbo.MyTable.MyPkColumn != @currentId
        and         dbo.MyTable.MyColumn = @proposedValue
    )

    RETURN case when @count = 0 then 1 else 0 end
END
GO
ALTER TABLE     MyTable
WITH CHECK
add constraint  CK_ColumnValueIsNullOrUnique
CHECK           ( 1 = dbo.IsValueNullOrUnique([MyColumn],[MyPkColumn]) )
GO