What is the difference between primary key, foreign key, and unique key in PostgreSQL? — PostgreSQL Interview Question #4

In PostgreSQL, a primary key, a foreign key and a unique key are used to enforce different types of integrity constraints on the data stored in a table.

- Primary key: A primary key is a column or a set of columns in a table that uniquely identifies each row in the table. It must have a unique value for each row, and it cannot have a NULL value. A table can have only one primary key. Primary keys are used to enforce entity integrity, which means that they ensure that each row in a table is unique and identifiable.

Example in Java:

Suppose we have a table named ‘students‘ with columns ‘id‘, ‘name‘, ‘age‘, and ‘gender‘. We can define the ‘id‘ column as a primary key like this:

CREATE TABLE students (
   id SERIAL PRIMARY KEY,
   name VARCHAR(50),
   age INTEGER,
   gender CHAR(1)
);

Here, we use the ‘SERIAL‘ data type to automatically generate a unique value for the ‘id‘ column for each new row inserted into the table. By making ‘id‘ the primary key, we ensure that each row in the ‘students‘ table is unique and identifiable by its unique ‘id‘.

- Foreign key: A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables, where the values in the foreign key column(s) of one table correspond to the values in the primary key column(s) of the other table. The purpose of a foreign key is to enforce referential integrity, which means that it ensures that the data in one table is consistent with the data in another table. A table can have multiple foreign keys.

Example in Java:

Suppose we have another table named ‘enrollments‘ with columns ‘student_id‘, ‘course_id‘, and ‘grade‘. We can define a foreign key on the ‘student_id‘ column that references the ‘id‘ column of the ‘students‘ table like this:

CREATE TABLE enrollments (
   student_id INTEGER REFERENCES students(id),
   course_id INTEGER,
   grade VARCHAR(2)
);

Here, we use the ‘REFERENCES‘ keyword to specify that the ‘student_id‘ column references the ‘id‘ column of the ‘students‘ table. This foreign key ensures that the ‘student_id‘ value in each row of the ‘enrollments‘ table corresponds to a valid ‘id‘ value in the ‘students‘ table.

- Unique key: A unique key is a column or a set of columns in a table that has a unique value for each row, similar to a primary key. However, unlike primary keys, unique keys can have NULL values. A table can have multiple unique keys. Unique keys are used to enforce domain integrity, which means that they ensure that the data in a column or a set of columns is unique.

Example in Java:

Suppose we have a table named ‘employees‘ with columns ‘id‘, ‘name‘, ‘email‘, and ‘phone‘. We can define a unique key on the ‘email‘ column to ensure that each email is used only once in the ‘employees‘ table like this:

CREATE TABLE employees (
   id SERIAL,
   name VARCHAR(50),
   email VARCHAR(50) UNIQUE,
   phone VARCHAR(20)
);

Here, we use the ‘UNIQUE‘ keyword to specify that the ‘email‘ column must have a unique value for each row. This unique key ensures that no two employees have the same email address.