PostgreSQL Unique Constraint on Columns

Here's the basic syntax for creating a UNIQUE constraint:

ALTER TABLE table_name
ADD UNIQUE (column1, column2);

Replace table_name with the actual name of your table, and replace column1 and column2 with the names of the columns you want to make unique together.

Example:

If you have a table named customers with columns email and phone_number, you can create a UNIQUE constraint to ensure that no two customers have the same email and phone number combination:

ALTER TABLE customers
ADD UNIQUE (email, phone_number);

This will prevent you from inserting rows into the customers table where the combination of email and phone_number already exists.

Additional points to consider:

• You can create a UNIQUE constraint when you create the table using the CREATE TABLE statement:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    UNIQUE (email, phone_number)
);

• You can also create a UNIQUE constraint on a subset of columns within a primary key. For example, if the id column in the customers table is the primary key, you can create a UNIQUE constraint on the email and phone_number columns within the primary key:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    UNIQUE (email, phone_number)
);

This will ensure that no two customers have the same email and phone number combination, even if their id values are different.

Example Codes for UNIQUE Constraints in PostgreSQL

Creating a UNIQUE Constraint When Creating a Table

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    UNIQUE (email, phone_number)
);

• Explanation:
  • This code creates a table named customers with columns id, email, and phone_number.
  • The UNIQUE (email, phone_number) clause ensures that no two rows can have the same combination of email and phone_number.

ALTER TABLE customers
ADD UNIQUE (email, phone_number);

• Explanation:

Creating a UNIQUE Constraint on a Subset of Columns Within a Primary Key

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    order_date DATE NOT NULL,
    UNIQUE (customer_id, order_date)
);

• Explanation:
  • The UNIQUE (customer_id, order_date) clause ensures that a customer cannot have multiple orders on the same date.

Dropping a UNIQUE Constraint

ALTER TABLE customers
DROP CONSTRAINT unique_email_phone_number;

• Explanation:

Alternative Methods for Enforcing Unique Constraints in PostgreSQL

While UNIQUE constraints are the most common and direct way to enforce unique combinations of columns in PostgreSQL, there are a few alternative approaches you can consider:

Check Constraints:

• Example:

  ALTER TABLE customers
  ADD CHECK (NOT EXISTS (SELECT 1 FROM customers WHERE email = NEW.email AND phone_number = NEW.phone_number AND id <> NEW.id));
  

  This check constraint ensures that no other row in the customers table has the same email and phone_number combination, except for the current row being inserted or updated.
• Syntax:

  ALTER TABLE table_name
  ADD CHECK (condition);
  

  Replace condition with an expression that evaluates to TRUE if the combination of columns is unique.
• Purpose: Can be used to define more complex conditions for uniqueness.

Triggers:

• Syntax:

  CREATE OR REPLACE FUNCTION check_unique_combination() RETURNS TRIGGER AS $$
  BEGIN
      IF EXISTS (SELECT 1 FROM customers WHERE email = NEW.email AND phone_number = NEW.phone_number AND id <> NEW.id) THEN
          RAISE EXCEPTION 'Duplicate combination of email and phone_number';
      END IF;
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  
  CREATE TRIGGER check_unique_combination_trigger
  BEFORE INSERT OR UPDATE ON customers
  FOR EACH ROW
  EXECUTE PROCEDURE check_unique_combination();
  

  This trigger function checks for duplicate combinations and raises an exception if found.
• Purpose: Can be used to perform actions before or after data modifications, including checking for unique combinations.

Application-Level Validation:

• Approach:
  • Validate the data in your application code using logic similar to check constraints or triggers.
  • Only send data to the database if it passes the validation.
• Purpose: Can be used to validate data before sending it to the database.

When to Choose Which Method:

• Application-level validation: When you need to enforce uniqueness before data reaches the database, or for additional validation logic.
• Triggers: For custom actions or error handling related to uniqueness.
• Check constraints: For more complex uniqueness conditions that cannot be expressed with a UNIQUE constraint.
• UNIQUE constraints: Simple, efficient, and should be preferred for most cases.