Postgres conditional unique constraint

Question

Pretend I have a users table where users are members of a specific tenant, and their e-mails are uniquely indexed to their tenant, like this:

User
id  |  tenant_id  |  email
1      1             [email protected]
2      1             [email protected]

This user is allowed because despite a duplicate e-mail, they are at a different tenant:

3      2             [email protected]

This user is prevented because the e-mail is a duplicate at the same tenant:

4      2             [email protected] <--- will throw an error

We have this much covered with a unique index -- that part is easy.

Now pretend that I want to be able to add a global user that can access all tenants, but only if the e-mail doesn't already exist in the table at all. Additionally, once the record exists, nobody else -- whether tenanted or not -- will be able to use the same e-mail.

For clarity, the global users could simply have a null tenant ID but we would likely also add a global boolean.

Is there a way to write constraints for this logic? You can't simply make e-mails globally uniquely constrained because they won't be able to be repeated across tenants, and if you index with a null tenant ID, postgres will allow an untenanted user if there are tenanted users with the same e-mail.

I've looked at exclusion constraints and checks but couldn't figure out how to combine them (uniquely constrain e-mail globally if tenant_id is null, and check for records with null tenant ID and matching e-mail when inserting any record).

Please don't ask why I'm doing things this way -- my table isn't actually users and we've considered and dismissed other architectures :)

Thanks in advance!

as I said "their e-mails are uniquely indexed to their tenant" -- we have the first part covered, it's easy. the question is regarding the second part. — jack_was_taken
– jack_was_taken, Commented Feb 17, 2017 at 19:50

Sharkman · Accepted Answer · 2017-02-20 14:59:15Z

3

According to PostgreSQL Documentation you can create unique partial index which will be effectively the same as creating unique partial constraint on your table:

CREATE UNIQUE INDEX some_index ON some_table (col_a) WHERE (col_b is null);

Using this technique you can create 2 separate unique indexes for admin and non-admin users.

answered Feb 20, 2017 at 14:59

Sharkman

13312 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

McNets · Accepted Answer · 2017-02-17 19:32:48Z

0

You can use a UNIQUE constraint for both fields:

create table myUsers
(
    id int not null,
    tenant int not null,
    email varchar(200) not null,
    UNIQUE(email, tenant)
);

insert into myUsers values
(1, 1, '[email protected]'),
(2, 1, '[email protected]');

insert into myUsers values
(3, 2, '[email protected]');

Next insert will throw an error:

insert into myUsers values
(4, 2, '[email protected]');

Error(s), warning(s):

23505: duplicate key value violates unique constraint "myusers_email_tenant_key"

Check it here: http://rextester.com/AJZVI34616

For the second part of the question:

Now pretend that I want to be able to add a global user that can access all tenants, but only if the e-mail doesn't already exist in the table at all.

One solution could be to reserve a tenant for admin users:

tenant = 0  <-- admin users

But the UNIQUE constraint allow duplicated emails, I recommend you to add a rol field to this table, or have another table of admin users for this purpose.

In my case, we use two tables, and both have a rol field.

edited Feb 17, 2017 at 19:32

answered Feb 17, 2017 at 19:23

McNets

10.9k3 gold badges39 silver badges70 bronze badges

2 Comments

jack_was_taken Over a year ago

Thank you for the answer, I should have been more clear that we have the first part covered already -- that's easy. Re: the second part, as I noted the table isn't actually for users, and while we considered alternative architectures this was by far the cleanest for our code. Just wanted to understand if the constraints I mentioned we possible.

jack_was_taken Over a year ago

* "were possible" (not "we possible")

Collectives™ on Stack Overflow

Postgres conditional unique constraint

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related