Primary keys are unique identifiers for each record. Include the key word `PRIMARY KEY NOT NULL` when defining the column to specify it as a primary key.
# Sequence as primary key
Use the data type BIGSERIAL or SERIAL when specifying integer type primary keys so that they will auto increment.
# Compound primary key
You can also set a compound primary key (e.g., two or more fields together). Be careful to specify fields that will never contain duplicates!
```SQL
-- SPECIFY IN CREATE TABLE
CREATE TABLE persons (
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
PRIMARY KEY (first_name, last_name)
);
-- OR USE ALTER TABLE
ALTER TABLE persons PRIMARY KEY (first_name, last_name);
```
# UUID primary key
A universally unique identifier (UUID) is a unique identifier that can be used as a primary key. The benefit of a UUID, as opposed to a sequence, is that it is unique across all tables (and even other databases). It also prevents hackers from mining a database by guessing primary keys.
To use in Postgres, you must first install the extension.
```SQL
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
```
Use `\df` to list the UUID generation algorithms available. You'll need to select one of these to generate a UUID when inserting a record. I recommend `uuid_generat_v4()`.
To create a primary key using UUID simply list `UUID` as the data type when creating the table. Foreign key references to a `UUID` should also use the data type `UUID`. When inserting a record, call the UUID function to create a unique identifier. You do not need to check for duplicates since these are guaranteed to be universally unique.
```SQL
CREATE TABLE cars (
car_uid UUID NOT NULL PRIMARY KEY
);
-- foreign key references should also be UUID
CREATE TABLE persons (
person_uid UUID
car_uid UUID references car(car_uid)
);
-- use a function to generate a uuid for each record
INSERT INTO persons (person_uid)
VALUES (uuid_generate_v4());
```