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
– Shannon Severance, Commented Aug 11, 2015 at 16:47
but it accept only one NULL value not multiple , check your table i think one NULL already exist — wiretext
– wiretext, Commented Aug 11, 2015 at 17:00
@tinka, yes I have one null in my table but it must accept more than one null. — mohaidar
– mohaidar, Commented Aug 11, 2015 at 17:04
@user3260672 no Not dba.stackexchange.com/questions/80514/… — wiretext
– wiretext, Commented Aug 11, 2015 at 17:06
See Is it possible for unique column to contain multiple null values SQL Server 2008 — stomy
– stomy, Commented Jan 23, 2018 at 21:29

GuidoG · Accepted Answer · 2015-08-11 18:59:29Z

40

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, 2015 at 18:59

GuidoG

12.3k6 gold badges59 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

dumbledad Over a year ago

Very useful - strange that it's not included in the examples of the WHERE clause in the CREATE INDEX documentation

Iman Over a year ago

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

Zeyad Over a year ago

That is very helpful, I was looking for this. Thank you @GuidoG

Benj Sanders Over a year ago

Entity Framework Migration, use like: Sql("CREATE UNIQUE INDEX [IX_PaymentId] ON [dbo].[Order]([PaymentId]) WHERE [PaymentId] IS NOT NULL;");

Oliver Nilsen Over a year ago

Important to note that your column must not have a UNIQUE constraint in its defintion and this should be handled via the Index.

Collectives™ on Stack Overflow

duplicate null value violation on UNIQUE KEY constraint in Mssql

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related