How do I create a unique constraint that also allows nulls

Question

I want to have a unique constraint on a column which I am going to populate with GUIDs  However  my data contains null values for this columns  How do I create the constraint that allows multiple null values   Here s an example scenario  Consider this schema   CREATE TABLE People     Id INT CONSTRAINT PK MyTable PRIMARY KEY IDENTITY    Name NVARCHAR 250  NOT NULL    LibraryCardId UNIQUEIDENTIFIER NULL    CONSTRAINT UQ People LibraryCardId UNIQUE  LibraryCardId      Then see this code for what I m trying to achieve   -- This works fine  INSERT INTO People  Name  LibraryCardId    VALUES   John Doe    AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA     -- This also works fine  obviously  INSERT INTO People  Name  LibraryCardId   VALUES   Marie Doe    BBBBBBBB-BBBB-BBBB-BBBB-BBBBBBBBBBBB     -- This would  correctly  fail  --INSERT INTO People  Name  LibraryCardId   --VALUES   John Doe the Second    AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA     -- This works fine this one first time  INSERT INTO People  Name  LibraryCardId   VALUES   Richard Roe   NULL    -- THE PROBLEM  This fails even though I d like to be able to do this  INSERT INTO People  Name  LibraryCardId   VALUES   Marcus Roe   NULL     The final statement fails with a message      Violation of UNIQUE KEY constraint  UQ People LibraryCardId   Cannot insert duplicate key in object  dbo People     How can I change my schema and or uniqueness constraint so that it allows multiple NULL values  while still checking for uniqueness on actual data

User · Answer

CREATE UNIQUE NONCLUSTERED INDEX  UIX COLUMN NAME  ON  dbo   Employee   Username  ASC  WHERE   Username  IS NOT NULL   WITH  ALLOW PAGE LOCKS   ON  ALLOW ROW LOCKS   ON  PAD INDEX   OFF  SORT IN TEMPDB   OFF   DROP EXISTING   OFF  IGNORE DUP KEY   OFF  STATISTICS NORECOMPUTE   OFF  ONLINE   OFF   MAXDOP   0  ON  PRIMARY

User · Answer

When I applied the unique index below    CREATE UNIQUE NONCLUSTERED INDEX idx badgeid notnull ON employee badgeid  WHERE badgeid IS NOT NULL    every non null update and insert failed with the error below      UPDATE failed because the following SET options have incorrect settings   ARITHABORT     I found this on MSDN     SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views  If SET ARITHABORT is OFF  CREATE  UPDATE  INSERT  and DELETE statements on tables with indexes on computed columns or indexed views will fail    So to get this to work correctly I did this     Right click  Database -- Properties-- Options-- Other   Options-- Misscellaneous-- Arithmetic Abort Enabled -- true   I believe it is possible to set this option in code using   ALTER DATABASE  DBNAME  SET ARITHABORT ON   but i have not tested this

User · Answer

SQL Server 2008    You can create a unique index that accept multiple NULLs with a WHERE clause  See the answer below   Prior to SQL Server 2008  You cannot create a UNIQUE constraint and allow NULLs  You need set a default value of NEWID      Update the existing values to NEWID   where NULL before creating the UNIQUE constraint

User · Answer

What you re looking for is indeed part of the ANSI standards SQL 92  SQL 1999 and SQL 2003  ie a UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values    In the Microsoft world of SQL Server however  a single NULL is allowed but multiple NULLs are not      In SQL Server 2008  you can define a unique filtered index based on a predicate that excludes NULLs   CREATE UNIQUE NONCLUSTERED INDEX idx yourcolumn notnull ON YourTable yourcolumn  WHERE yourcolumn IS NOT NULL    In earlier versions  you can resort to VIEWS with a NOT NULL predicate to enforce the constraint

User · Answer

For people who are using Microsoft SQL Server Manager and want to create a Unique but Nullable index you can create your unique index as you normally would then in your Index Properties for your new index  select  Filter  from the left hand panel  then enter your filter  which is your where clause    It should read something like this     YourColumnName  IS NOT NULL    This works with MSSQL 2012

User · Answer

It is possible to create a unique constraint on a Clustered Indexed View     You can create the View like this   CREATE VIEW dbo VIEW OfYourTable WITH SCHEMABINDING AS SELECT YourUniqueColumnWithNullValues FROM dbo YourTable WHERE YourUniqueColumnWithNullValues IS NOT NULL      and the unique constraint like this   CREATE UNIQUE CLUSTERED INDEX UIX VIEW OFYOURTABLE    ON dbo VIEW OfYourTable YourUniqueColumnWithNullValues

User · Answer

Create a view that selects only non-NULL columns and create the UNIQUE INDEX on the view   CREATE VIEW myview AS SELECT    FROM    mytable WHERE   mycolumn IS NOT NULL  CREATE UNIQUE INDEX ux myview mycolumn ON myview  mycolumn    Note that you ll need to perform INSERT s and UPDATE s on the view instead of table   You may do it with an INSTEAD OF trigger   CREATE TRIGGER trg mytable insert ON mytable INSTEAD OF INSERT AS BEGIN         INSERT         INTO    myview         SELECT            FROM    inserted END

User · Answer

Maybe consider an  INSTEAD OF  trigger and do the check yourself  With a non-clustered  non-unique  index on the column to enable the lookup

User · Answer

You can t do this with a UNIQUE constraint  but you can do this in a trigger       CREATE TRIGGER  dbo   OnInsertMyTableTrigger     ON   dbo   MyTable     INSTEAD OF INSERT AS  BEGIN     SET NOCOUNT ON       DECLARE  Column1 INT      DECLARE  Column2 INT  -- allow nulls on this column      SELECT  Column1 Column1   Column2 Column2 FROM inserted       -- Check if an existing record already exists  if not allow the insert      IF NOT EXISTS SELECT   FROM dbo MyTable WHERE Column1  Column1 AND Column2  Column2  Column2 IS NOT NULL      BEGIN         INSERT INTO dbo MyTable  Column1  Column2              SELECT  Column2   Column2      END     ELSE     BEGIN         RAISERROR  The unique constraint applies on Column1  d  AND Column2  d  unless Column2 is NULL    16  1   Column1   Column2           ROLLBACK TRANSACTION         END  END

User · Answer

It can be done in the designer as well   Right click on the Index   Properties to get this window

User · Answer

this code if u make a register form with textBox and use insert and ur textBox is empty and u click on submit button    CREATE UNIQUE NONCLUSTERED INDEX  IX tableName Column  ON  dbo   tableName   columnName  ASC  WHERE  columnName

User · Answer

SQL Server 2008 And Up  Just filter a unique index   CREATE UNIQUE NONCLUSTERED INDEX UQ Party SamAccountName ON dbo Party SamAccountName  WHERE SamAccountName IS NOT NULL    In Lower Versions  A Materialized View Is Still Not Required  For SQL Server 2005 and earlier  you can do it without a view  I just added a unique constraint like you re asking for to one of my tables  Given that I want uniqueness in column SamAccountName  but I want to allow multiple NULLs  I used a materialized column rather than a materialized view   ALTER TABLE dbo Party ADD SamAccountNameUnique    AS  Coalesce SamAccountName  Convert varchar 11   PartyID    ALTER TABLE dbo Party ADD CONSTRAINT UQ Party SamAccountName    UNIQUE  SamAccountNameUnique    You simply have to put something in the computed column that will be guaranteed unique across the whole table when the actual desired unique column is NULL  In this case  PartyID is an identity column and being numeric will never match any SamAccountName  so it worked for me  You can try your own method   be sure you understand the domain of your data so that there is no possibility of intersection with real data  That could be as simple as prepending a differentiator character like this   Coalesce  n    SamAccountName   p    Convert varchar 11   PartyID     Even if PartyID became non-numeric someday and could coincide with a SamAccountName  now it won t matter   Note that the presence of an index including the computed column implicitly causes each expression result to be saved to disk with the other data in the table  which DOES take additional disk space   Note that if you don t want an index  you can still save CPU by making the expression be precalculated to disk by adding the keyword PERSISTED to the end of the column expression definition   In SQL Server 2008 and up  definitely use the filtered solution instead if you possibly can   Controversy  Please note that some database professionals will see this as a case of  surrogate NULLs   which definitely have problems  mostly due to issues around trying to determine when something is a real value or a surrogate value for missing data  there can also be issues with the number of non-NULL surrogate values multiplying like crazy    However  I believe this case is different  The computed column I m adding will never be used to determine anything  It has no meaning of itself  and encodes no information that isn t already found separately in other  properly defined columns  It should never be selected or used   So  my story is that this is not a surrogate NULL  and I m sticking to it  Since we don t actually want the non-NULL value for any purpose other than to trick the UNIQUE index to ignore NULLs  our use case has none of the problems that arise with normal surrogate NULL creation   All that said  I have no problem with using an indexed view instead   but it brings some issues with it such as the requirement of using SCHEMABINDING  Have fun adding a new column to your base table  you ll at minimum have to drop the index  and then drop the view or alter the view to not be schema bound   See the full  long  list of requirements for creating an indexed view in SQL Server  2005   also later versions    2000    Update  If your column is numeric  there may be the challenge of ensuring that the unique constraint using Coalesce does not result in collisions  In that case  there are some options  One might be to use a negative number  to put the  surrogate NULLs  only in the negative range  and the  real values  only in the positive range  Alternately  the following pattern could be used  In table Issue  where IssueID is the PRIMARY KEY   there may or may not be a TicketID  but if there is one  it must be unique   ALTER TABLE dbo Issue ADD TicketUnique    AS  CASE WHEN TicketID IS NULL THEN IssueID END   ALTER TABLE dbo Issue ADD CONSTRAINT UQ Issue Ticket AllowNull    UNIQUE  TicketID  TicketUnique     If IssueID 1 has ticket 123  the UNIQUE constraint will be on values  123  NULL   If IssueID 2 has no ticket  it will be on  NULL  2   Some thought will show that this constraint cannot be duplicated for any row in the table  and still allows multiple NULLs

User · Answer

As stated before  SQL Server doesn t implement the ANSI standard when it comes to UNIQUE CONSTRAINT  There is a ticket on Microsoft Connect for this since 2007  As suggested there and here the best options as of today are to use a filtered index as stated in another answer or a computed column  e g    CREATE TABLE  Orders       OrderId  INT IDENTITY 1 1  NOT NULL     TrackingId  varchar 11  NULL           ComputedUniqueTrackingId  AS         CASE WHEN  TrackingId  IS NULL       THEN       cast  OrderId  as varchar 12         ELSE  TrackingId Unique  END        CONSTRAINT  UQ TrackingId  UNIQUE   ComputedUniqueTrackingId

User · Answer

You can create an INSTEAD OF trigger to check for specific conditions and error if they are met   Creating an index can be costly on larger tables   Here s an example   CREATE TRIGGER PONY trg pony unique name ON PONY tbl pony  INSTEAD OF INSERT  UPDATE  AS BEGIN  IF EXISTS      SELECT TOP  1  1      FROM inserted i     GROUP BY i pony name     HAVING COUNT 1   gt  1                  OR EXISTS      SELECT TOP  1  1      FROM PONY tbl pony t     INNER JOIN inserted i     ON i pony name   t pony name           THROW 911911   A pony must have a name as unique as s he is  --PAS   16   ELSE     INSERT INTO PONY tbl pony  pony name  stable id  pet human id      SELECT pony name  stable id  pet human id     FROM inserted  END

[sql-server] How do I create a unique constraint that also allows nulls?

Examples related to sql-server

Examples related to tsql