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

In SQL Server 2008 you can use a filtered unique index. In 2005 an indexed view with a where clause. — Martin Smith, Feb 23 '12 at 12:00
possible duplicate of [Simple CHECK Constraint not so simple](http://stackoverflow.com/questions/7210680/simple-check-constraint-not-so-simple) — Martin Smith, Feb 23 '12 at 12:01
@Martin Smith: Nope, but partial duplicate of http://stackoverflow.com/questions/866061 — Stefan Steiger, Jul 03 '12 at 11:38

score 3 · Answer 1 · 2012-02-23T12:26:48.090

3

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.

edited Feb 23 '12 at 12:26

answered Feb 23 '12 at 12:00

That is not the distinction between a unique index and a unique constraint. It is the distinction between a filtered and a non filtered unique index. – Martin Smith Feb 23 '12 at 12:02
Great ! Anything is better than a trigger. – Stefan Steiger Feb 23 '12 at 12:22
@MartinSmith Huh, you're right, a foreign key that references a unique index does work. Thanks. Some parts of the documentation need updating, then. – Feb 23 '12 at 12:25
@hvd could you point out where the documentation implies otherwise? – Aaron Bertrand Feb 23 '12 at 14:24
@AaronBertrand http://msdn.microsoft.com/en-us/library/ms175464.aspx "A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table." Technically it's true, but it's misleading. – Feb 23 '12 at 14:36
Thanks I've submitted a re-wording suggestion. I'll try to remember to follow up here but keep in mind that Books Online changes can literally take months to filter out into the public documentation. In other words, I may forget, but feel free to ping me about it if you don't see a change by summer. :-) – Aaron Bertrand Feb 23 '12 at 14:52
@AaronBertrand Thanks, and no worries, I'm already happy knowing it's possible :) – Feb 23 '12 at 15:33
@hvd: You were wrong. I unmarked your answer as accepted answer. I've added the real answer, works on SQL-Server 2008 & 2005 ;) – Stefan Steiger Jul 03 '12 at 11:31
@Quandary Your answer doesn't add a unique constraint, it adds an overly complicated check constraint that has the same effect as the unique index in my answer. A unique constraint isn't possible. – Jul 03 '12 at 14:51
@hvd: Not overly complex. I needed to be able to switch the check on and off (conditional) based on the customer-id (retrieved from the application configuration table for customer X), which is the reason I came up with this (you can't access another table or a function in a unique index). It does add a constraint, and the constraint checks uniqueness based on conditions. While pedantically it's true that my solution technically isn't a unique constraint but only a constraint, it comes closer to the desired thing than a unique index. Though no question about it, if you don't need per customer – Stefan Steiger Jul 04 '12 at 09:57
@hvd: configuration, then a unique index is far simpler and works just as well. Additionally, my question was also tagged SQL-Server **2005**, and a unique index with a where clause only works on SQL-Server 2008, while my solution works on SQL-Server 2005 as well ;) For anybody that can use SQL-Server 2008 and doesn't need a **per customer** condition, but only a **generally appliable** condition, I still strongly recommend a unique index for the sake of simplicity. – Stefan Steiger Jul 04 '12 at 10:00
@Quandary Being able to turn off the check based on fields in different tables isn't part of the question as asked, and a unique constraint is closer to a unique index than it is to a check constraint (I'm serious, look up the differences between a unique constraint and a unique index if you like), but good point about the SQL Server 2005 incompatibility, I did miss that. I *think* a unique nonfiltered index on a filtered view will work for SQL Server 2005, and that would also allow linking to other tables, but I would need to work that out to be sure. – Jul 04 '12 at 11:07

score 0 · Accepted Answer · edited May 23 '17 at 11:56

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]

score 0 · Answer 3 · answered Feb 23 '12 at 12:08

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

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

3 Answers3