I've been experimenting with nullable columns in composite pk's, and I seem to have discovered some inconsistencies in Firebird's behaviour.

First, if we create a table like this:

);

and we try to create a primary key on (field1, field2) FB will complain that field2 cannot be used as primary key, because it's nullable. If, on the other hand, we create both columns as not null, create the primary key, and then drop the not null constraint on the 2nd column (which, surprinsingly, is allowed by the engine), everything seems to work as I would expect: null is just treated as a normal value for the field2, and no duplications ever occur (if you drop not null constraint on all fields of a pk, however, you can insert any number of records that have all these columns nulled).

I guess I understand why the whole primary key shouldn't be nullable (doesn't make much sense to me anyway, although I don't understand why the engine cares), but why can't part of it be so? I mean, why can't the record ('a', null) be just like any ('a', n) record in the A table above for the pk (field1, field2)?

PS: just wondering, really. The problem I had in hands has been solved with a GUID pk and a unique constraint for the composite nullable fields.