PostgreSQL

The UNIQUE constraint in PostgreSQL ensures that all values in a column or a set of columns are unique across the rows of a table. This constraint is crucial for preventing duplicate data entries and maintaining the integrity of your database. By using the UNIQUE constraint, you ensure that certain columns, such as email addresses or account numbers, do not contain duplicate values. Here's a guide to using the UNIQUE constraint with practical examples for the customers, accounts, and transactions tables in a banking system.

Using UNIQUE Constraint in PostgreSQL

1. Basic Syntax for UNIQUE Constraint

   CREATE TABLE table_name (
       column_name data_type UNIQUE
   );
   

   • table_name: The name of the table.
   • column_name: The name of the column to apply the uniqueness constraint.
   • data_type: The data type of the column.

   Example: Creating a customers Table with UNIQUE Constraints

   CREATE TABLE customers (
       customer_id SERIAL PRIMARY KEY,
       first_name VARCHAR(50) NOT NULL,
       last_name VARCHAR(50) NOT NULL,
       email VARCHAR(100) UNIQUE,  -- Ensure email is unique across all customers
       phone_number VARCHAR(15) UNIQUE  -- Ensure phone number is unique across all customers
   );
   

2. UNIQUE Constraint on Multiple Columns
   • You can apply the UNIQUE constraint to a combination of columns to ensure that the combination of values is unique across rows.
   • Syntax:

   CREATE TABLE table_name (
       column1 data_type,
       column2 data_type,
       UNIQUE (column1, column2)
   );
   

   Example: Creating an accounts Table with UNIQUE Constraints on Multiple Columns

   CREATE TABLE accounts (
       account_id SERIAL PRIMARY KEY,
       customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
       account_number VARCHAR(20) UNIQUE,  -- Ensure account number is unique
       UNIQUE (customer_id, account_number)  -- Ensure a customer cannot have duplicate account numbers
   );
   

3. Adding UNIQUE Constraints to Existing Tables
   • You can add a UNIQUE constraint to an existing column or a set of columns using ALTER TABLE.
   • Syntax:

   ALTER TABLE table_name
   ADD CONSTRAINT constraint_name UNIQUE (column_name);
   

   Example: Adding UNIQUE Constraint to transactions Table

   ALTER TABLE transactions
   ADD CONSTRAINT unique_transaction UNIQUE (account_id, transaction_date, amount);
   

4. Dropping UNIQUE Constraints
   • To remove a UNIQUE constraint, you need to use ALTER TABLE and DROP CONSTRAINT.
   • Syntax:

   ALTER TABLE table_name
   DROP CONSTRAINT constraint_name;
   

   Example: Dropping UNIQUE Constraint from accounts Table

   ALTER TABLE accounts
   DROP CONSTRAINT accounts_account_number_key;  -- The constraint name will vary
   

5. Considerations When Using UNIQUE Constraints
   • Index Creation: PostgreSQL automatically creates an index for columns with a UNIQUE constraint, which can improve query performance but also affect write operations.
   • Error Handling: Attempting to insert or update a row with duplicate values in a column with a UNIQUE constraint will result in a unique violation error.
   • Design Impact: Carefully consider which columns should have unique constraints to balance data integrity and application requirements.

This guide should help beginners understand and apply the UNIQUE constraint in PostgreSQL. By ensuring that values are unique where necessary, you can maintain the accuracy and integrity of your database, avoiding issues related to duplicate data.

To gain complete access, login with gmail or outlook, no need of signup. click here