Verify unique sql value with constraint

Question

I have a situation where a table has three column identifiers, value and status. For an individual identifier, there must be only one status with a value of 1, and it must be allowed for the identifier to have more than one status with a value of 0. A unique key would prevent an identifier with more than one status (0 or 1).

Is there a way to solve this problem, possibly using restrictions?

thanks

Answer 1

You can create an indexed view that will support your restriction on keeping the ID unique to [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 can use a unique filtered index instead.

Answer 2

If the table can have duplicate ID values, then the check constraint will not work for your situation. I think the only way is to use a trigger. If you are looking for an example, I can post it. But in the end, use the trigger to check if the inserted / updated ID has a status of 1, which is duplicated by the same identifier.

EDIT: You can always use a unique constraint for ID and Value . I think this will give you what you are looking for.

Answer 3

You can put this in the insert / update trigger to make sure that there is only one combination with a value of 1; if your condition is not met, you can throw an error using a trap and force the operation to be thrown back.

Answer 4

If you can use NULL instead of 0 for null status, then you can use the UNIQUE constraint for a pair, and it should work. Since NULL is not the actual value (NULL! = NULL), then strings with multiple zeros should not conflict.

Answer 5

IMHO, this is mainly a normalization problem. A column named "id" does not uniquely address a row, so it can never be PK. At least a new (surrogate) key (element) is required. The constraint itself cannot be expressed as an expression "inside the string", so it must be expressed through FK.

Thus, it is divided into two tables: One with PK = id and FK REFERENCING two.sid

Two with PK = surrogate key and FK id LINKS one.id The original value of the "payload" is also used here.

The “one bit variable” disappears because it can be expressed in EXISTS terms. (effectively, table one points to a string containing a token)

[I expect that the Postgres rule system can be used to use the above two-table model to emulate the alleged behavior of the OP. But that would be an ugly hack ...]

EDIT / UPDATE:

Postgres supports partial / conditional indexes. (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; 

Answer 6

 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 

Answer 7

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 

The second ones create a table constraint

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

Thus, one identifier can have one status (1) and the maximum possible status (0).