Unable to insert NULL value in MS SQL Server 2000

[Manu]

I am unable to insert record in a field which is null and unique. It does not accept NULL value again in the same field. It shows error UNIQUE KEY VIOLATION.
Please help

[Ray Miao]

Can't create unique index or constraint on the column that accepts null value.


------------
Manu at 8/26/2002 8:25:33 AM

I am unable to insert record in a field which is null and unique. It does not accept NULL value again in the same field. It shows error UNIQUE KEY VIOLATION.
Please help

[Paul]

Hi,
You can insert NULL value, but since you have a unique constraint on the table, you can atmost have 1 null value for the column. If you need to have null inserted on the column, then remove the unique constraint.
-Paul


------------
Manu at 8/26/2002 8:25:33 AM

I am unable to insert record in a field which is null and unique. It does not accept NULL value again in the same field. It shows error UNIQUE KEY VIOLATION.
Please help

[Manu]

But NULL is not equal to NULL. Then there is no question of UNIQUE key violation.


------------
Paul at 8/26/2002 4:07:30 PM

Hi,
You can insert NULL value, but since you have a unique constraint on the table, you can atmost have 1 null value for the column. If you need to have null inserted on the column, then remove the unique constraint.
-Paul


------------
Manu at 8/26/2002 8:25:33 AM

I am unable to insert record in a field which is null and unique. It does not accept NULL value again in the same field. It shows error UNIQUE KEY VIOLATION.
Please help

[Manu]

My case is -

A field named employee email in employee detail table. Now at the time of populating employee details a user may or may not have his email address but may want to store available information. Hence it is not necessary that email address is know but if it is entered it should be unique.

So, what do you suggest for this case?


------------
Manu at 8/26/2002 8:25:33 AM

I am unable to insert record in a field which is null and unique. It does not accept NULL value again in the same field. It shows error UNIQUE KEY VIOLATION.
Please help

[David Westmore]

Manu, you've hit on one of the less well known but rather important differences between Access and SQL Server...you're right when you say that NULL is not equal to NULL, but unfortunately SQL Server doesn't agree with you.

There is the "ignore null values" box in the window that appears when you define a unique index, but I've never quite understood what it means (it certainly doesn't solve your problem).

One solution could be to keep the E-mail addresses in a separate table (which would be in a 1-to-1, or should I say 1-to-0, relationship)..in the E_Mail table you can create a unique index, because if you don't have the E-mail address there won't be a record...obviously in all queries that require a join between your main table and the one with the E-Mail addresses, you would have to use LEFT JOIN to make sure that you include customers without an E-mail address.

This is tacky, however, I don't really like 1-to-1 relationships.

NULL is a funny concept, because sometimes it means "correlated record doesn't exist" and sometimes it means something like "field is empty", but I personally like NULLs.

Regards

David Westmore



------------
Manu at 8/27/2002 1:20:34 AM

But NULL is not equal to NULL. Then there is no question of UNIQUE key violation.


------------
Paul at 8/26/2002 4:07:30 PM

Hi,
You can insert NULL value, but since you have a unique constraint on the table, you can atmost have 1 null value for the column. If you need to have null inserted on the column, then remove the unique constraint.
-Paul


------------
Manu at 8/26/2002 8:25:33 AM

I am unable to insert record in a field which is null and unique. It does not accept NULL value again in the same field. It shows error UNIQUE KEY VIOLATION.
Please help

[David Westmore]

Another possible solution would be to write a trigger instead of using a constraint on the index, it depends if you like coding stuff in T-SQL (I don't).

Regards

David Westmore



------------
David Westmore at 8/27/2002 2:58:03 AM

Manu, you've hit on one of the less well known but rather important differences between Access and SQL Server...you're right when you say that NULL is not equal to NULL, but unfortunately SQL Server doesn't agree with you.

There is the "ignore null values" box in the window that appears when you define a unique index, but I've never quite understood what it means (it certainly doesn't solve your problem).

One solution could be to keep the E-mail addresses in a separate table (which would be in a 1-to-1, or should I say 1-to-0, relationship)..in the E_Mail table you can create a unique index, because if you don't have the E-mail address there won't be a record...obviously in all queries that require a join between your main table and the one with the E-Mail addresses, you would have to use LEFT JOIN to make sure that you include customers without an E-mail address.

This is tacky, however, I don't really like 1-to-1 relationships.

NULL is a funny concept, because sometimes it means "correlated record doesn't exist" and sometimes it means something like "field is empty", but I personally like NULLs.

Regards

David Westmore



------------
Manu at 8/27/2002 1:20:34 AM

But NULL is not equal to NULL. Then there is no question of UNIQUE key violation.


------------
Paul at 8/26/2002 4:07:30 PM

Hi,
You can insert NULL value, but since you have a unique constraint on the table, you can atmost have 1 null value for the column. If you need to have null inserted on the column, then remove the unique constraint.
-Paul


------------
Manu at 8/26/2002 8:25:33 AM

I am unable to insert record in a field which is null and unique. It does not accept NULL value again in the same field. It shows error UNIQUE KEY VIOLATION.
Please help

[Manu]

Thank you all for your great concern. I agree to the solution David has given. I would prefer to remove UNIQUE key from NULL fields as the project is in ASP and now this will be managed by programming guys. But I really feel Microsoft themself don't know the database concept. There a strange thing I have experienced if someone can explain?

I created a table and then to add PRIMARY KEY using ALTER command I first have to add NOT NULL using ALTER command and then only I can add PRIMARY KEY. Isn't it strange as PRIMARY KEY means UNIQUE and NOT NULL. So why to add NOT NULL explicitely?


------------
Manu at 8/26/2002 8:25:33 AM

I am unable to insert record in a field which is null and unique. It does not accept NULL value again in the same field. It shows error UNIQUE KEY VIOLATION.
Please help