Working with identifiers

---

Working with IDs (Identifiers) is one of the most critical decisions in database design. The ID serves as the Primary Key (PK)—the unique address for every row in your table.

Here is a breakdown of how to handle SQL IDs, from the standard integer approach to using UUIDs, and how to modify them.

---

The Standard: Integer IDs (Auto-Increment)

For most applications, the default choice is an integer that counts up ($1, 2, 3...$) automatically.

Pros:

• Performance: Integers are small (4 bytes) and very fast to index and join.

• Sequential: Data is inserted in order, which is healthy for database indexes (B-Trees).

• Human-readable: It is easy to say "Look at User ID 500."

How to create them:

Syntax varies slightly by database engine.

MySQL / MariaDB

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

PostgreSQL

-- Uses a specific 'serial' pseudo-type
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

SQL Server

CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    username VARCHAR(50)
);

---

The Alternative: UUIDs

A UUID (Universally Unique Identifier) is a 128-bit label (e.g., a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11).

Pros:

• Global Uniqueness: You can generate an ID in your application code before inserting it into the database, without worrying about collisions. This is essential for distributed systems or microservices.

• Security: If your URL is .../user/10, a hacker can guess .../user/11. With UUIDs, ids are unguessable.

Cons:

• Size: They are 4x larger than integers (16 bytes vs 4 bytes). This bloats indexes.

• Fragmentation: Because they are random, new rows are inserted randomly into the index, which can hurt write performance (unless you use UUID v7 which is time-sorted).

How to create them (PostgreSQL example):

Note: PostgreSQL is best for UUIDs as it has a native UUID data type. MySQL stores them as CHAR(36) or BINARY(16

-- Step 1: Ensure the extension for generating UUIDs is active
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Step 2: Create table
CREATE TABLE orders (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    order_total DECIMAL(10, 2)
);

---

Changing ID Columns

Modifying an ID column on an existing table is high-risk because it usually involves Foreign Keys (other tables pointing to this ID).

A. Renaming the Column

This is safe providing you update your application code to match.

-- PostgreSQL / MySQL 8.0+
ALTER TABLE users RENAME COLUMN id TO user_id;

B. Changing Data Type (e.g., Integer to BigInt)

If you are running out of numbers (hitting the ~2.1 billion limit of INT), you need to upgrade to BIGINT.

• Warning: This operation often locks the table, meaning downtime for large datasets. It also requires you to update every Foreign Key in other tables that point to this ID.

-- PostgreSQL example
-- 1. Drop constraints (Foreign keys pointing here)
-- 2. Alter the type
ALTER TABLE users ALTER COLUMN id TYPE BIGINT;
-- 3. Recreate constraints

C. Moving from Integer to UUID

This is a complex migration. You generally cannot "convert" the data easily. The typical strategy is:

1. Add a new uuid_id column.

2. Backfill it with generated UUIDs for all existing rows.

3. Change your app to read the new column.

4. Eventually, make the new column the Primary Key.

---

Summary Comparison

Feature	Integer (Auto-Increment)	UUID (v4)
Storage Size	Very Small (4 bytes)	Large (16 bytes)
Read/Write Speed	Fastest	Slower (Random I/O)
Security	Low (Guessable)	High (Obfuscated)
Distributed Systems	Difficult (Collisions possible)	Excellent (No collisions)

Recommendation

• Use Integers (BigInt) for internal data, analytics, and high-performance joins.

• Use UUIDs for public-facing IDs (URLs, API keys) or if you are building a system with many databases syncing together.

---