postgresql. uniqueness for two columns

Question

I need to create unique index for table with two fields (email, alternative_email). It means one email address can be mentioned only one time in two columns. Also alternative email can be empty.

CREATE TABLE customers (
   id serial PRIMARY KEY,
   email VARCHAR (255) NOT NULL,
   alternative_email VARCHAR (255) NOT NULL default ''
);

Data:

insert into customers (email, alternative_email) 
values ('[email protected]', ''); - ok

insert into customers (email, alternative_email) 
values ('[email protected]', '[email protected]');

Second row should not be inserted because alternative_email = '[email protected]' already mentioned as email in first row.

How to create index to do this ?

What about the alternative_email? Should that also be unique across all rows? — user330315
– user330315, Commented Oct 28, 2019 at 11:00
Unrelated, but: NOT NULL default '' seems like an odd choice if you don't require an alternative email — user330315
– user330315, Commented Oct 28, 2019 at 11:06
Something like this maybe? create unique index on customers ( (coalesce(nullif(alternative_email, ''), email))); — user330315
– user330315, Commented Oct 28, 2019 at 11:28
No, it doesn't. I managed insert row. id | email | alternative_email ----+-------------------+------------------- 1 | [email protected] | [email protected] 2 | [email protected] | [email protected] — Nickolay
– Nickolay, Commented Oct 28, 2019 at 11:57

jbx · Accepted Answer · 2024-04-08 08:58:25Z

2

You cannot create an index across two columns like that. What you need to do is change your design such that emails are all in one column in a different table.

So you could have a table called emails like:

CREATE TABLE emails (
   id serial PRIMARY KEY,
   email VARCHAR (255) NOT NULL UNIQUE,
);

Then have a many-to-many table that maps the customers to emails:

CREATE TABLE customer_emails (
   customer_id INTEGER NOT NULL REFERENCES customers(id),
   email_id INTEGER NOT NULL UNIQUE REFERENCES emails(id),
   alternative boolean NOT NULL DEFAULT FALSE,
   PRIMARY KEY (customer_id, email_id)
);

CREATE UNIQUE INDEX customer_email_idx ON customer_emails(customer_id, alternative);

The unique on email_id enforces that no 2 different customers can use the same email address, and the primary key further enforces that the same email address cannot be used for both primary and alternative email addresses of the same customer (a bit redundant given the first constraint, but just in case you want to drop the first one).

The composite unique index ensures that a customer can have at most one primary and one alternative email address.

edited Apr 8, 2024 at 8:58

answered Oct 28, 2019 at 11:05

jbx

22.3k20 gold badges100 silver badges151 bronze badges

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

3 Comments

user330315 Over a year ago

If those were integers you can actually create such a constraint: stackoverflow.com/a/40935314 but unfortunately that doesn't work with varchar arrays

user330315 Over a year ago

Yes, on integer arrays to be precise.

jbx Over a year ago

Good to know about it. So if the OP decided to put email addresses in a separate table, the foreign keys would then be integer IDs which then could be put in an integer array with your technique too.

Collectives™ on Stack Overflow

postgresql. uniqueness for two columns

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related