How to add a unique constraint on several columns, with a condition?

Question

Question:

I want to add a unique constraint on a mapping table (n:n).
I want that new values may be inserted, but only if TEST_FK_UID, TEST_DateFrom and TEST_DateTo are not equal to an already existing entry.

The problem is the status field.
Status 1 means active..
Status != 1 means inactive/deleted..
.
.
So one may of course insert a new entry with the same FK, DateFrom and DateTo, IF - and only if - the status of the existing entry (all existing entries, as you can insert, delete, insert, delete, insert, delete, etc.) is != 1

Here is what I have so far:

CREATE TABLE dbo._________Test  
(
     TEST_UID uniqueidentifier NOT NULL 
    ,TEST_FK_UID uniqueidentifier NOT NULL 
    ,TEST_DateFrom DateTime NOT NULL 
    ,TEST_DateTo DateTime NOT NULL  
    ,TEST_Status int NOT NULL 
    ,UNIQUE(TEST_FK_UID, TEST_DateFrom, TEST_DateTo, TEST_Status) 
); 

Answer 1

It is very possible, like this
(basic credit goes to: https://stackoverflow.com/users/103075):

---

Edit:
OK, pedantically seen it's not a unique constraint, it's a check constraint, but WTF - it has the same effect and works on SQL-Server 2005 as well, and the (conditional) condition is configurable per customer (replace SET @bNoCheckForThisCustomer = 'false' with a select to a configuration table) - that's not possible with a unique index AFAIK ... ;)

---

Note this line:

AND ZO_RMMIO_UID != @in_ZO_RMMIO_UID

(ZO_RMMIO_UID is the unique primary key of the n:n mapping table)
It's important, since a check constraint seems to be similar to a onAfterInsert trigger.
If this line is missing, it checks on itselfs as well, which leads to the function always returning true...

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CheckNoDuplicate_T_ZO_AP_Raum_AP_Ref_Mietobjekt]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_AP_Raum_AP_Ref_Mietobjekt]'))
ALTER TABLE [dbo].[T_ZO_AP_Raum_AP_Ref_Mietobjekt] DROP CONSTRAINT [CheckNoDuplicate_T_ZO_AP_Raum_AP_Ref_Mietobjekt]
GO





IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_InsertCheck_IsDuplicate_T_ZO_AP_Raum_AP_Ref_Mietobjekt]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fu_InsertCheck_IsDuplicate_T_ZO_AP_Raum_AP_Ref_Mietobjekt]
GO





-- ========================================================================
-- Author:            Me
-- Create date:       09.08.2010
-- Last modified:     09.08.2010
-- Description:   Conditionally check if row is a duplicate
-- ========================================================================

-- PRE:  UID, Valid RM_UID, Valid MIO_UID, 
--       Valid datetime-from for db usr language, valid datetime-to for db usr language
-- POST: True/False


CREATE  FUNCTION [dbo].[fu_InsertCheck_IsDuplicate_T_ZO_AP_Raum_AP_Ref_Mietobjekt](@in_ZO_RMMIO_UID uniqueidentifier, @in_ZO_RMMIO_RM_UID AS uniqueidentifier, @in_ZO_RMMIO_MIO_UID as uniqueidentifier, @in_ZO_RMMIO_DatumVon AS datetime, @in_ZO_RMMIO_DatumBis AS datetime)
    RETURNS bit
AS
    BEGIN   

        DECLARE @bIsDuplicate AS bit
        SET @bIsDuplicate = 'false'     


        DECLARE @bNoCheckForThisCustomer AS bit
        SET @bNoCheckForThisCustomer = 'false'

        IF @bNoCheckForThisCustomer = 'true'
            RETURN @bIsDuplicate 




        IF EXISTS
        (
            SELECT 
                 ZO_RMMIO_UID
                ,ZO_RMMIO_RM_UID
                ,ZO_RMMIO_MIO_UID
            FROM T_ZO_AP_Raum_AP_Ref_Mietobjekt 
            WHERE ZO_RMMIO_Status = 1 
            AND ZO_RMMIO_UID != @in_ZO_RMMIO_UID
            AND ZO_RMMIO_RM_UID = @in_ZO_RMMIO_RM_UID 
            AND ZO_RMMIO_MIO_UID = @in_ZO_RMMIO_MIO_UID 
            AND ZO_RMMIO_DatumVon = @in_ZO_RMMIO_DatumVon 
            AND ZO_RMMIO_DatumBis = @in_ZO_RMMIO_DatumBis 
        )
            SET @bIsDuplicate = 'true'

        RETURN @bIsDuplicate
    END


GO




ALTER TABLE [dbo].[T_ZO_AP_Raum_AP_Ref_Mietobjekt]  WITH NOCHECK ADD  CONSTRAINT [CheckNoDuplicate_T_ZO_AP_Raum_AP_Ref_Mietobjekt] 
CHECK  
(
    NOT 
    (
        dbo.fu_InsertCheck_IsDuplicate_T_ZO_AP_Raum_AP_Ref_Mietobjekt(ZO_RMMIO_UID, ZO_RMMIO_RM_UID, ZO_RMMIO_MIO_UID, ZO_RMMIO_DatumVon, ZO_RMMIO_DatumBis) = 1 
    )
)
GO


ALTER TABLE [dbo].[T_ZO_AP_Raum_AP_Ref_Mietobjekt] CHECK CONSTRAINT [CheckNoDuplicate_T_ZO_AP_Raum_AP_Ref_Mietobjekt]
GO

And here a test case:

CREATE TABLE [dbo].[T_ZO_AP_Raum_AP_Ref_Mietobjekt](
    [ZO_RMMIO_UID] [uniqueidentifier] NOT NULL,  -- <== PRIMARY KEY
    [ZO_RMMIO_RM_UID] [uniqueidentifier] NOT NULL,
    [ZO_RMMIO_MIO_UID] [uniqueidentifier] NOT NULL,
    [ZO_RMMIO_DatumVon] [datetime] NOT NULL,
    [ZO_RMMIO_DatumBis] [datetime] NOT NULL,
    [ZO_RMMIO_Status] [int] NOT NULL,
    [ZO_RMMIO_Bemerkung] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



/*
DELETE FROM T_ZO_AP_Raum_AP_Ref_Mietobjekt 
WHERE ZO_RMMIO_Status = 1 
AND ZO_RMMIO_RM_UID = '2007B6F5-9010-4979-AB39-00057DA353C0' 
AND ZO_RMMIO_MIO_UID = 'FFA177E9-971E-4500-805D-00116F708E7B'
*/


INSERT INTO T_ZO_AP_Raum_AP_Ref_Mietobjekt
(
     ZO_RMMIO_UID
    ,ZO_RMMIO_RM_UID
    ,ZO_RMMIO_MIO_UID
    ,ZO_RMMIO_DatumVon
    ,ZO_RMMIO_DatumBis
    ,ZO_RMMIO_Status
    ,ZO_RMMIO_Bemerkung
)
VALUES
(
     NEWID() --<ZO_RMMIO_UID, uniqueidentifier,>
    ,'2007B6F5-9010-4979-AB39-00057DA353C0' --<ZO_RMMIO_RM_UID, uniqueidentifier,>
    ,'FFA177E9-971E-4500-805D-00116F708E7B' --<ZO_RMMIO_MIO_UID, uniqueidentifier,>
    ,'01.01.2012' --<ZO_RMMIO_DatumVon, datetime,>
    ,'31.12.2999' --<ZO_RMMIO_DatumBis, datetime,>
    ,1 --<ZO_RMMIO_Status, int,>
    ,NULL--<ZO_RMMIO_Bemerkung, text,>
)
GO



INSERT INTO T_ZO_AP_Raum_AP_Ref_Mietobjekt
(
     ZO_RMMIO_UID
    ,ZO_RMMIO_RM_UID
    ,ZO_RMMIO_MIO_UID
    ,ZO_RMMIO_DatumVon
    ,ZO_RMMIO_DatumBis
    ,ZO_RMMIO_Status
    ,ZO_RMMIO_Bemerkung
)
VALUES
(
     NEWID() --<ZO_RMMIO_UID, uniqueidentifier,>
    ,'2007B6F5-9010-4979-AB39-00057DA353C0' --<ZO_RMMIO_RM_UID, uniqueidentifier,>
    ,'FFA177E9-971E-4500-805D-00116F708E7B' --<ZO_RMMIO_MIO_UID, uniqueidentifier,>
    ,'01.01.2012' --<ZO_RMMIO_DatumVon, datetime,>
    ,'31.12.2999' --<ZO_RMMIO_DatumBis, datetime,>
    ,1 --<ZO_RMMIO_Status, int,>
    ,NULL--<ZO_RMMIO_Bemerkung, text,>
)
GO

SELECT [ZO_RMMIO_UID]
      ,[ZO_RMMIO_RM_UID]
      ,[ZO_RMMIO_MIO_UID]
      ,[ZO_RMMIO_DatumVon]
      ,[ZO_RMMIO_DatumBis]
      ,[ZO_RMMIO_Status]
      ,[ZO_RMMIO_Bemerkung]
  FROM [T_ZO_AP_Raum_AP_Ref_Mietobjekt]

Answer 2

You cannot. You can, however, create a unique index. It functions similarly, and I expect well enough for you.

CREATE UNIQUE INDEX MyIndex
ON _________Test
( TEST_FK_UID
, TEST_DateFrom
, TEST_DateTo )
WHERE TEST_Status = 1

The most important difference between a unique index and a unique constraint is that you cannot create a foreign key in another table that references a unique index. Edit: as Martin points out, this is not true, a foreign key can reference a nonfiltered unique index.

Answer 3

Use Instead Of trigger on INSERT,UPDATE operations.. and check the existing values with the values in the INSERTED table(which is created in the case of triggers) If the status in the INSERTED table id 1 AND if it is unique, do the insertion operation or just abort with some messages..