duplicate null value violation on UNIQUE KEY constraint in Mssql

Question

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?

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
@tinka, yes I have one `null` in my table but it must accept more than one `null`. — Mohamad Haidar, Aug 11 '15 at 17:04
@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
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

score 28 · Accepted Answer · answered Aug 11 '15 at 18:59

28

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;

answered Aug 11 '15 at 18:59

GuidoG

10,365
5
41
71

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

duplicate null value violation on UNIQUE KEY constraint in Mssql

1 Answers1

Linked