UNIQUE constraint not working on a column in postgresql

Question

Below is the schema:

BEGIN;

DROP TABLE IF EXISTS contacts;

CREATE TABLE contacts (
  user_id SERIAL,
  first_name VARCHAR NOT NULL,
  last_name VARCHAR NOT NULL,
  email VARCHAR NOT NULL,
  phone_number VARCHAR NOT NULL,
  status VARCHAR NOT NULL,
  created_at TIMESTAMP DEFAULT current_timestamp,
  UNIQUE (phone_number, email)
);

COMMIT;

and below is the code for inserting the data in database using express and pg-promise:

function createContact(req, res, next) {
  db.none('insert into contacts(first_name, last_name, email, phone_number, status)' +
      'values(${first_name}, ${last_name}, ${email}, ${phone_number}, ${status})',
    req.body)
    .then(() => {
      res.status(200)
        .json({
          status: 'success',
          message: 'Inserted one contact',
        });
    })
    .catch(err => next(err));
}

The UNIQUE constraint doesn't seem to work on email column. I can add duplicate values in email column. The constraint works only on Phone number.

I have installed the database using EDB Postgres installer for Windows 10. I am using PGADMIN 4 and PSQL Shell for data manipulation.

ghostprgmr · Accepted Answer · 2017-04-08 14:55:11Z

7

UNIQUE (phone_number, email)

tells, that a pair <phone_number, email> should be unique, not each column separately.

Try to set unique constraint for separate columns.

Check out Constraints

answered Apr 8, 2017 at 14:55

ghostprgmr

4882 silver badges11 bronze badges

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

Comments

Adam · Accepted Answer · 2017-04-08 14:56:55Z

3

If you have UNIQUE (phone_number, email) that means, that the touple (phone_number, email) must be unique. So you can have duplicated emails as long as they are with different phone numbers (and vice versa).

You want something like that: UNIQUE (phone_number), UNIQUE (email).

answered Apr 8, 2017 at 14:56

Adam

5,6296 gold badges35 silver badges41 bronze badges

Collectives™ on Stack Overflow

UNIQUE constraint not working on a column in postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related