Question

What is a primary key in a relational database? Discuss its role in ensuring data integrity and how it differs from other types of keys. Include examples of when and why to use primary keys in your database schema.

Requirements

1. Define what a primary key is.
2. Explain its significance in maintaining data integrity.
3. Provide examples of primary keys in common database scenarios.
4. Discuss differences between primary keys and foreign keys.

Definition of Primary Key

A primary key is a unique identifier for a record in a table, ensuring that no two records can have the same value in that column.

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL
);

Data Integrity

Primary keys enforce entity integrity by ensuring that each record is unique and not null, preventing duplicate entries.

INSERT INTO users (username) VALUES ('alice'); -- user_id will auto-increment

Difference from Foreign Key

While a primary key uniquely identifies a record in its own table, a foreign key is used to link two tables together by referencing the primary key of another table.

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(user_id)
);