Checking sql unique value with constraint

Question

I have a situation where a table has three columns ID, Value and status. For a distinct ID there should be only one status with value 1 and it should be allowed for ID to have more then one status with value 0. Unique key would prevent ID of having more then one status (0 or 1).

Is there a way to solve this, maybe using constraints?

Thanks

Subinfeudation

What RDBMS/version are you using? – Pleuron

Should there be exactly one Status 1 row per ID or no more than one? I.e. is it allowed to have an ID without a Status 1 row? – Outdare

possible duplicate of Unique constraint within a group of records where some value is the same – Outdare

Answer 1

You can create an indexed view that will uphold your constraint of keeping ID unique for [Status] = 1.

create view dbo.v_YourTable with schemabinding as
select ID
from dbo.YourTable
where [Status] = 1

go

create unique clustered index UX_v_UniTest_ID on v_YourTable(ID)

In SQL Server 2008 you could use a unique filtered index instead.

Thine

This will not prevent extra rows on the underlying data. – Krystalkrystalle

@JeremyHolovacs - It will allow duplicate ID's for [Status] = 0 but not for [Status] = 1. That is how I read the question. Or do you have something else in mind? – Thine

Hah missed the schemabinding. Yes this will work. Bit roundabout, but effective. – Krystalkrystalle

Answer 2

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.

Elongation

i would appreciate a suggestion on how to create this trigger. Is there a way for this trigger to generate an error such SqlException 2627 for invalid unique value? – Subinfeudation

I'm also curious if you can do this. I tried to create a row-level trigger once in Oracle that queried for other rows in the same table, and it would just throw a "table is mutating" error. Anyone know if MS SQL has this same limitation? – Evaporate

Same as above... a composite unique key with a nullable column will not allow more than one combination of a specific value and a null value in Sql 2005. – Krystalkrystalle

Answer 3

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.

Krystalkrystalle

Seriously? Downvoting this with no explanation? This will work. – Krystalkrystalle

Answer 4

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.

Evaporate

+1 for recommening a change of design, instead of just conforming to the OP's structure. I like that as well. – Elongation

Yea I only know this due to trying to do the exact opposite once. I wanted a check constraint preventing multiple nulls and was all "WTH doesn't this work??" – Evaporate

That doesn't work so well in SQL server, although it should (at least as far as 2005 is concerned)... – Krystalkrystalle

It should as per the SQL standard. I can confirm it indeed works in Postgres. I apologize, as I posted my answer before the OP updated what DB they were using. – Evaporate

since the status column allows values 0 and 1 and I want to enforce a restriction of only one status=1 per ID, how about adding restriction that involves sum(status) <=1 grouped by ID? is this possible via trigger or constraint? – Subinfeudation

No, I don't believe you can define a constraint on the sum of values across a group. I believe a trigger is your most viable option here, sorry to say. Though another option is to remove INSERT/UPDATE permissions to your table and do all updates through a sproc, which would check the data first. – Evaporate

I agree this should work according to the sql standard, but it still won't work in Sql 2005. A composite unique key with a nullable column will allow exactly one combination of a specific number and a null value. – Krystalkrystalle

Answer 5

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;

Esperance

This example will not run in Sql 2005. – Krystalkrystalle

I know, but 4 out of 5 of the OP's tags are not plaform-specific. Moreover, the underlying problem is more of a data modelling kind of problem than a "solve my problem on my platform" thing. For data modelling stuff it is IMHO always good to know that different solutions could exist on different implementations. – Esperance

Answer 6

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).

Subinfeudation

This will be effective, but VERY inefficient. For large data operations, this will destroy the performance of your server. – Krystalkrystalle

Please explain why is this a resource consuming operation? – Subinfeudation

user defined functions are notoriously slow for a system designed for set-based operations. UDFs can be used for this purpose, but larger set-based operations against this table will be very resource-intensive. Mikael's solution or using a trigger will be much more efficient. – Krystalkrystalle

Answer 7

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

Floorage