Duplicate keys

Navigation
1

** Improving the Post for a better understanding of what happened. Person, good afternoon.

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

Why logic, there are not 2 emails equal ...
Only now when I'm going to do an insert in the database and the value of the email is null it returns me saying that I can not have this duplicate key.

  

"Violation of UNIQUE KEY constraint 'UQ__CLIENT__161CF72400F78D67'.   Can not insert duplicate key in object 'dbo.CLIENTE'. The duplicate key   value is (). The statement has been terminated. "

How do I deprive myself of "valid" emails to register duplicate and accept more than 1 customer 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 I enter my first value with the null email field

INSERT INTO TEST VALUES('LUIZ', NULL)

Then I try the second value:

INSERT INTO TEST VALUES('JUCA', NULL)

When I try to insert this value it accuses me that the key values can not be duplicated, it can not have 2 values (because it has the constraint 'unique')

    
asked by anonymous 08.09.2018 / 19:55

2 answers

3

You should have created the table with the email statement like this:

email varchar(max) unique

I put max just as an example. This will generate a constraint that will not allow duplicate values, including null .

As you've tagged the sql-server-2012 tag, for version you have an alternative (from the 2008 version onwards actually):

  • Remove unique from the email column;

  • Create an index unique that accepts nulls, like this:

    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.

    
08.09.2018 / 20:32
2

In index creation you should use this clause: 

WHERE email IS NOT NULL;
    
08.09.2018 / 20:01
Exiting PHP script with "exit" would delete all variables and destroy objects? Transform array vue into Json