Unique Constraint column can only contain one NULL value

Yes, it's "specific" to Microsoft SQL Server (in that some other database systems have the opposite approach, the one you expected - and the one defined in the ANSI standard, but I believe there are other database systems that are the same as SQL Server).

If you're working on a version of SQL Server that supports filtered indexes, you can apply one of those:

CREATE UNIQUE INDEX IX_T ON [Table] ([Column]) WHERE [Column] IS NOT NULL

(But note that this index cannot be the target of an FK constraint)

The "Why" of it really just comes down to, that's how it was implemented long ago (possibly pre-standards) and it's one of those awkward situations where to change it now could potentially break a lot of existing systems.

Re: Foreign Keys - you would be correct, if it wasn't for the fact that a NULL value in a foreign key column causes the foreign key not to be checked - there's no way (in SQL Server) to use NULL as an actual key.

Yes it's a SQL Server feature (and a feature of a few other DBMSs) that is contrary to the ISO SQL standard. It perhaps doesn't make much sense given the logic applied to nulls in other places in SQL - but then the ISO SQL Standard isn't very consistent about its treatment of nulls either. The behaviour of nullable uniqueness constraints in Standard SQL is not very helpful. Such constraints aren't necessarily "unique" at all because they permit duplicate rows. E.g., the constraint UNIQUE(foo,bar) permits the following rows to exist simultaneously in a table:

foo    bar------ ------999    NULL999    NULL

(!)

Avoid nullable uniqueness constraints. It's usually straightforward to move the columns to a new table as non-nullable columns and put the uniqueness constraint there. The information that would have been represented by populating those columns with nulls can (presumably) be represented by simply not populating those columns in the new table at all.