'Non-'Unique indexing

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.

enter

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.