We wanted to make a column in our MySQL database table (using InnoDB) nullable, but use the column as part of a composite key.
unique key C1,C2,C3,C4
C4 can be null
C1 C2 C3 C4
enter the following:
x y z a
goes into the table ok
enter those again - error that we break unique constraint.
This is as expected.
x y z null
goes into table ok
enter those again - they also enter fine, and select * from table shows two separate row entries.
So, basically, you can have a nullable field in a composite unique index, but if the column is null, you loose the unique index checking.
I don't know about other DB's, but it is a shame that the null can't be part of the uniqueness of the index.