How do I set up an index on a pair of columns so that the values are tied together?

Question

I have a table with id, external_id and country_code columns. I have two rules which I want the database to impose:

• Each External ID can only appear once per country code
• Each ID can only appear with at most one non-null External ID, and vice versa.

The first rule is easy enough - I add a unique multi-column index to external_id and country_code. How do I get it to impose the second?

Answer 1

I'm not sure I understand the second constraint exactly, but in this case it looks like a trigger tied to INSERT and UPDATE events would solve your problem.

Answer 2

It sounds almost like your externalid should have a unique index too, if your saying that an externalid can only link to a single id.