Why unique takes only one null as we know all null are not equal?

[Tanu Sharma]

If Null= Null or <some value>= Null are not valid or in other words null values can't be compared then why unique constraint takes only one null value.

[Rabbit]

Who said null values can't be compared? They can be compared.

[Tanu Sharma]

Agreed. As null can not be considered as a value rather a marker which indicates absence of value and a value if compared with null results in an unknown. My question is for unique constraint. In SQL server we are not allowed to enter more then one null if there is a unique constraint present for same column. Could you please help me with the answer?

[ck9663]

Here, read this...

Good Luck!!!

~~ CK

[Tanu Sharma]

Thanks Ck.
But this couldn't answer my question as this solution consider all nulls equal and that's why it's grouping them together. It would be a great help for me if you could explain concept of Null in databases.

[Rabbit]

In SQL Server, nulls are considered equal and are indexed.

[Tanu Sharma]

I agree to your fact, Rabbit.
But interestingly this is contradictory to the fact that joins exclude null values. We can find nulls in a table if using outer joins only. Inner joins and cross joins, also available in standard SQL, do not generate Null placeholders for missing values in related tables.

If we see in MySQL or PostgreSQL, they allow more than one nulls in a unique column, considering two nulls as different.

According to one of the PostgreSQL documentation when an index is declared unique, multiple table rows with equal indexed values will not be allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all of the indexed columns are equal in two rows.

May be this has not followed by Microsoft SQL Server.

[Rabbit]

Who said you can't inner join on a null? You can inner join on a null.

SELECT *
FROM Table1
INNER JOIN Table2
ON Table1.ID = Table2.ID
  OR (Table1.ID IS NULL AND Table2.ID IS NULL)

Some database management servers will extend unique constraints to nulls. SQL Server is one of them. PostgreSQL is not.

[ck9663]

I can only guess, but I think the reason why sql server did that is because you cannot compare NULL, as you said yourself. Since you cannot compare NULL, you would not know if one NULL is the same or different from the other. For the purpose of group aggregation (which uses the same logic as DISTINCT), it consider all NULLs as one and the same.

Hope that make sense...

Good Luck!!!

~~ CK

[Tanu Sharma]

Agree to you CK. :-)