Comment 3 for bug 1412950

"Null values are not considered equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows."

I think you've got it. The problem is due to both these sentences together. It means that ('page1', 1, NULL, NULL) does not "equal" ('page1', 1, NULL, NULL), and so the uniqueness constraint allows it.

Apparently the problem we're facing is what some call "polymorphic associations". See http://stackoverflow.com/questions/7947218/polymorphic-association-foreign-key-constraints-is-this-a-good-solution and http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back/32

The problem with the (owner, ownertype) approach, is that it doesn't work with Postgres foreign keys. And it would require a rather substantial refactoring of a large table, during the next database upgrade.

I think a smaller solution that might also work, is to just split it into three indexes:

1. (urlid, author)
2. (urlid, group)
3. (urlid, institution)

Although in practice, dropping and recreating indexes also tends to take a while...