Yeah the big kick in the pants I got this year was using nullable columns in composite keys in MySQL and finding out that when their value is null, the result key is considered unique, because NULL is literally unknowable. So:

  col A, Col B
  null, 1
  null, 1

Is not constrained, but:

  col A, Col B
  1, 1
  1, 1 <--- NOT ALLOWED

I guess this is one of those things that is completely obvious to those that know it.