Hello!

I want to implement something that will act as a UNIQUE constraint on a table, but I need a little more flexibility.

I want to implement a three-column UNIQUE constraint. However, one of the columns may be NULLs and I would want, essentially, that the NULLs be considered equal amongst them.

Each of the columns define part of a scheme to identify objects. For instance, a row could have the values (1, 3, NULL). This would translate to a CQp.kVI.3, for instance. On the other hand, I don't want another (1,3,NULL) row to be added to the DB.

Any ideas?

I believe you've got two approaches you could take: add either a before insert/update trigger, or a rule, to monitor inserts/updates to table.field2, and when the count of records with field2 is null (and field1 = new.field1) then raise an error if using a trigger, or an 'Instead do nothing' if using rules.

Although I've needed to use it very little, the rule system is the preferred approach when it will work for you.

Thank you! I'll try the rules system.