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.
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 🙂