We therefore make sure, withing the database, that names are unique. However, no other table in the database relates to the collections table by the collection Name. That would be very inefficient. Should not allow NULLs. Are updateable, so long as they are kept unique.
NULLs are acceptable.
Well, you must understand that indices are implementation-dependent. SQL does not define the implementation, merely the relations between data in the database. Postgres does allow non-unique indices, but indices used to enforce SQL keys are always unique. Thus, you may query a table by any combination of its columns, despite the fact that you don't have an index on these columns.