Difference between Primary key Unique Key in Greenplum

In Greenplum, which is based on PostgreSQL, the concepts of primary keys and unique keys are quite similar to PostgreSQL. Let's outline the differences and provide an example:

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, the differences between primary keys and unique constraints in Greenplum are essentially the same as in PostgreSQL, given that Greenplum is derived from PostgreSQL and largely maintains its behavior and features in terms of database schema design.