Difference between Primary key Unique Key in PostgreSQL

In PostgreSQL, both primary keys and unique constraints (similar to unique keys in MariaDB) are used to enforce uniqueness, but they have different characteristics and use cases.

1. Primary Key:

   - A primary key uniquely identifies each row in a table.

   - There can be only one primary key in a table.

   - Primary key columns cannot contain NULL values.

   - By default, a primary key creates a unique index on the column(s) it is defined on.

   - A table can have only one primary key, but that primary key can consist of multiple columns (composite primary key).

Example:

CREATE TABLE students (

    student_id SERIAL PRIMARY KEY,

    student_name VARCHAR(100),

    email VARCHAR(100) UNIQUE

);

In this example, student_id is the primary key of the students table. It uniquely identifies each student. Additionally, the email column has a unique constraint, ensuring that each email in the table is unique.

2. Unique Constraint:

   - A unique constraint ensures that all values in a column (or a combination of columns) are distinct.

   - Unlike primary keys, unique constraints can contain NULL values. However, if a column is defined as NOT NULL, the unique constraint ensures that each value in that column is unique.

   - A table can have multiple unique constraints.

   - Unique constraints can be used to enforce uniqueness on columns that are not designated as the primary key.

Example:

Extending the previous example, let's add a unique constraint:

CREATE TABLE students (

    student_id SERIAL PRIMARY KEY,

    student_name VARCHAR(100),

    email VARCHAR(100) UNIQUE,

    student_code VARCHAR(20) UNIQUE

);

In this modified example, email continues to be unique, but now we've introduced another unique constraint on the student_code column. This ensures that each student has a unique student code. However, unlike the primary key constraint, NULL values are allowed in the student_code column (assuming it's not defined as NOT NULL).

In summary, while both primary keys and unique constraints enforce uniqueness, the primary key uniquely identifies each row and is often used as the main identifier for a table, whereas unique constraints provide additional constraints on columns that are not designated as the primary key.