I have a table that looks like this:

Each user has an email and alternative email. I want to make it so that when a user is added (or modified), the PSQL table does not allow duplicates, even between the two columns. For example, this would not be allowed:

Because [email protected] is duplicated. How can I accomplish this?

CodePudding user response：

The easiest alternative - without changing the data model - would be to control it within a trigger function:

Declare the columns independently as UNIQUE, so that there are no duplicated e-mails in the same column:

CREATE TABLE t (
  id int, 
  email text UNIQUE, 
  alt_email text UNIQUE
);

Create a function to check if the e-mails exists in another column:

CREATE OR REPLACE FUNCTION check_email() 
RETURNS TRIGGER AS $$
BEGIN  
  IF (SELECT EXISTS 
       (SELECT 1 FROM t WHERE alt_email = NEW.email OR email = NEW.alt_email)) THEN
    RAISE EXCEPTION 'E-mail already exists!';
  ELSE
    RETURN NEW;
  END IF;
END; $$ LANGUAGE plpgsql;

And finally attach the trigger function to the table

CREATE TRIGGER tgr_check_email
BEFORE INSERT OR UPDATE ON t
FOR EACH ROW EXECUTE PROCEDURE check_email();

Demo: db<>fiddle

CodePudding user response：

Neither unique index nor constraint will help you there. You can:

1. Create a INSERT/UPDATE trigger that will check the emails. A locking mechanism must be implemented to disallow concurrent inserts/updates as checks are performed in different transactions - that may harm your performance.
2. Move your emails to separate table as this is a one-to-many relationship. On this new table (let's call it email) you can add a unique index or even have an email as a primary key. This is the right and most clean solution to your problem. You can create a view with a name and the columns the same as your current table to move with the change quicker.
3. Do both of 1 and 2: create the email table and add the trigger maintaining the records of it based on the email and alt_email values. You will have the email dublicates but you will not have to change anything in your application - all the changes go into DB schema.