CODEWITHSUNDEEP
postgresql
shet_tayyy
shet_tayyy

Reputation: 5755

UNIQUE constraint not working on a column in postgresql

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.

Upvotes: 1

Views: 2662

Answers (2)

Adam
Adam

Reputation: 5599

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

Upvotes: 3

ghostprgmr
ghostprgmr

Reputation: 488

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

Upvotes: 7

Related Questions