Uniqueness constraint for elements of arrays in the entire table

All we need is an easy explanation of the problem, so here it is.

In the following table:

CREATE TABLE user (
    email           varchar[],
    ...
);

I’d like to ensure that no two users have the same e-mail. I’m not sure how the UNIQUE constraint interacts with array data types. What’s the appropriate way to ensure uniqueness? Or is this an anti-pattern and should I just declare another emails table instead?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Yes, as you said, I’d create an emails table that refer to the users.

CREATE TABLE users (
    id_user         ... ,
    username        varchar(255),
    ... ,
    PRIMARY KEY(id_user)
);

CREATE TABLE emails (
    id_email        ... ,
    id_user         ... ,
    email           varchar(255),
    ...,
    PRIMARY KEY(id_email),
    CONSTRAINT fk_user
      FOREIGN KEY(id_user) 
      REFERENCES users(id_user)
);

Then in the emails table you can add a unique constraint on id_user, id_email.

CREATE UNIQUE INDEX CONCURRENTLY xak1_unique_id_user_and_email_ix
ON emails (id_user, id_email);

ALTER TABLE emails ADD CONSTRAINT xak1_unique_id_user_and_email UNIQUE
                      USING INDEX xak1_unique_id_user_and_email_ix;

P.S. I don’t work usually on PostgreSQL, for the syntax I referred to link

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply