17

MS SQL Server does not ignore the null value and considers it as violation for the UNIQUE KEY constraint but what I know is that the UNIQUE KEY differ from the primary key where it accepts the null value.

Violation of UNIQUE KEY constraint 'AK_UserName'. Cannot insert duplicate key in object 'dbo.users'. The duplicate key value is (<NULL>).
The statement has been terminated.

Can anyone help me to solve this problem?

Mo Haidar
  • 3,748
  • 6
  • 37
  • 76
  • 3
    Create a filtered unique index, where column is not null. That way the index does not include the null values. – Shannon Severance Aug 11 '15 at 16:47
  • but it accept only one `NULL` value not multiple , check your table i think one `NULL` already exist – wiretext Aug 11 '15 at 17:00
  • 1
    @tinka, yes I have one `null` in my table but it must accept more than one `null`. – Mo Haidar Aug 11 '15 at 17:04
  • 1
    @user3260672 no Not http://dba.stackexchange.com/questions/80514/why-does-a-unique-constraint-allow-only-one-null – wiretext Aug 11 '15 at 17:06
  • 1
    See [Is it possible for unique column to contain multiple null values SQL Server 2008](https://dba.stackexchange.com/a/81293) – stomy Jan 23 '18 at 21:29

1 Answers1

36

you can create a unique index that ignores null values like this

CREATE UNIQUE NONCLUSTERED INDEX idx_col1
ON dbo.MyTable(col1)
WHERE col1 IS NOT NULL;
GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • 1
    Very useful - strange that it's not included in the examples of the `WHERE` clause in the [CREATE INDEX documentation](https://msdn.microsoft.com/en-gb/library/ms188783.aspx) – dumbledad Mar 01 '16 at 16:43
  • 1
    note that WITH ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] must be placed after the where of course – Iman Feb 27 '17 at 11:04
  • 1
    That is very helpful, I was looking for this. Thank you @GuidoG – Zeyad Nov 22 '19 at 03:38
  • Entity Framework Migration, use like: Sql("CREATE UNIQUE INDEX [IX_PaymentId] ON [dbo].[Order]([PaymentId]) WHERE [PaymentId] IS NOT NULL;"); – Benj Sanders Sep 28 '20 at 09:10
  • Important to note that your column must not have a UNIQUE constraint in its defintion and this should be handled via the Index. – Oliver Nilsen Dec 07 '21 at 10:35
  • Oliver, your comment makes no sense. The code in the answer is creating an unique index on the column, why would that column already need one before you could create another ? – GuidoG Jun 21 '22 at 07:55