Duplicate keys

Question

** Improving the Post to better understand what happened. Person, good afternoon.

I created a table 'client' in sql server and put the email field as vachar(50), Unique and null accept

pq by logic, there are no 2 equal emails...
Only now when I’m going to do an Insert in the bank and the value of the email is null it returns to me saying that I can’t have this duplicate key.

"Violation of UNIQUE KEY Constraint 'UQ__CLIENTE__161CF72400F78D67'. Cannot Insert Duplicate key in Object 'dbo.CLIENT'. The Duplicate key value is (). The statement has been terminated."

As I deprive to not let "validated" emails register duplicated and accept more than 1 client with email = null?

Exemplifying the problem.

Created the table

CREATE TABLE TEST( ID_TEST INT NOT NULL IDENTITY, NOME VARCHAR(50) NOT NULL, EMAIL VARCHAR(20) PRIMARY KEY(ID_TEST), constraint VALOR_NULO UNIQUE(EMAIL) )

Then enter my first value with the null email field

INSERT INTO TEST VALUES('LUIZ', NULL)

Then I try second:

INSERT INTO TEST VALUES('JUCA', NULL)

When I try to enter this value it accuses me that the key values can not be duplicated, can not have 2 values(why is with the 'Unique Constraint'')

Answer 1

You must have created the table with the email statement so:

email varchar(max) unique

Placed max just as an example. This will generate a Constraint which will not allow duplicate values, including null.

How did you tag sql-server-2012, for version you have an alternative (from version 2008 onwards in fact):

• Remove the unique of the email column;

• Create an index unique which accepts nulls, thus:

  CREATE UNIQUE NONCLUSTERED INDEX idx_clienteemail
  ON client(email) WHERE email IS NOT NULL

This will validate the unique value for email and will allow nulls.

Answer 2

When creating the index you should use this clause:

WHERE email IS NOT NULL;

I put in the Github for future reference.