Pirate Wiki

Toggle Theme

Keys ​

Keys are the mechanism we use to uniquely identify rows within a table and to establish relationships between different tables. They are essential for maintaining data integrity.

Primary Key (PK) ​

A Primary Key is a column (or a set of columns) that uniquely identifies every row in a table.

  • A table can have only one Primary Key constraint.
  • The Primary Key column(s) cannot contain NULL values.
  • The values must be unique across the entire table.
sql
-- The 'id' column is the Primary Key
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

Default Behavior

In PostgreSQL, defining a column as a PRIMARY KEY automatically creates a unique B-tree index on that column, making lookups by the primary key extremely fast.

Foreign Key (FK) ​

A Foreign Key is a column (or a set of columns) in one table that refers to the Primary Key in another table. The Foreign Key enforces referential integrity.

  • It ensures that the value in the child table must exist in the parent table.
  • A table can have multiple Foreign Keys.
sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    user_id INT,
    -- user_id references the 'id' column in the 'users' table
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
);

Unique Key ​

A Unique Key constraint ensures that all values in a column are distinct.

  • Unlike a Primary Key, a table can have multiple Unique Key constraints.
  • In standard SQL, Unique Keys can typically contain NULL values (and multiple NULLs are allowed because NULL is not equal to NULL).
sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE -- No two users can have the same email
);

Composite Key ​

A Composite Key is a Primary Key (or Unique/Foreign Key) that consists of more than one column. This is commonly used in "join tables" to resolve many-to-many relationships.

sql
CREATE TABLE enrollments (
    student_id INT,
    class_id INT,
    enrollment_date DATE,
    -- The combination of student_id and class_id is unique
    PRIMARY KEY (student_id, class_id) 
);