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?

Reputation: 25993

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

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?

Upvotes: 0

Answers (2)

kon5ad

Reputation: 139

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.

Upvotes: 0

Gintautas Miliauskas

Reputation: 7892

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.

Upvotes: 1

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

Answers (2)

Related Questions