CODEWITHSUNDEEP
postgresql
Nickolay
Nickolay

Reputation: 45

postgresql. uniqueness for two columns

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 ?

Upvotes: 1

Views: 495

Answers (1)

jbx
jbx

Reputation: 22188

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.

Upvotes: 2

Related Questions