Primary Key

PRIMARY KEY sets the unique identifier, it allows two or more columns. A primary key columns implicitly have a NOT NULL and UNIQUE constraints.

TODO: int vs GUIDs (base on the course said, GUIDs is less efficient when matching foreign key)

Multi Columns

It is common in Many-to-Many relationships.

Indexing

When the primary key is set, Postgres automatically creates a unique B-tree index:

CREATE UNIQUE INDEX table_name_pkey ON table_name (col1, col2);

Learn More:Index

Auto Increment

IDENTITY

Is portable to other DBs and has two modes:

• GENERATED ALWAYS (refuses manual INSERTs)
• GENERATED BY DEFAULT (allows manual INSERTs when needed)

id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

SERIAL

Is a legacy Postgres shortcut (~ PostgreSQL 9), not a SQL-standard type, not recommended. It can be manually inserted.

It is a macro that expands into:

1. Create an integer column
2. Create a sequence
   1. Set that column’s default to nextval (sequence)
   2. Mark the sequence as “owned by” that column