User Authentication Schema
What you'll build
A SQL schema for a user authentication system with a users table, unique email constraint, an index on email for fast lookups, and a roles column.
Goals
- Create a users table with authentication fields
- Enforce unique email with a constraint
- Create an index for fast email lookups
- Add a CHECK constraint for valid roles
Prerequisites
- CREATE TABLE and constraints
- Understanding of indexes
- Basic authentication concepts
Steps
- 1
Create the users table
Create a
userstable with id, email, password_hash, role, and timestamps. Email must be unique and not null.CodeCREATE TABLE users ( id SERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL, role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')), created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW() );Expected result
The users table is created with a unique email and a CHECK on role.
- 2
Create an index on email
Create an index on the email column to speed up login queries, which look up users by email.
CodeCREATE INDEX idx_users_email ON users(email);Expected result
An index is created, making email lookups fast.
- 3
Insert users
Insert a regular user and an admin. Use placeholder password hashes (in real apps, use bcrypt or argon2).
CodeINSERT INTO users (email, password_hash, role) VALUES ('ada@example.com', 'hash_ada_123', 'user'), ('admin@example.com', 'hash_admin_456', 'admin');Expected result
Two users are inserted — one regular and one admin.
- 4
Query for login
Write the query that would be used at login time — find a user by email. The index makes this fast.
CodeSELECT id, email, password_hash, role FROM users WHERE email = 'ada@example.com';Expected result
The query returns Ada's user record instantly using the index.
- 5
Add indexes to optimize queries
Create indexes on frequently queried columns to improve query performance. Indexes are the primary means of database performance optimization, but be mindful of write overhead.
Code-- Create indexes on frequently filtered columns CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_created_at ON orders(created_at DESC); -- Verify the index is used EXPLAIN SELECT * FROM orders WHERE user_id = 42;Expected result
EXPLAIN output shows an index scan using idx_orders_user_id instead of a full table scan.
Try it yourself
What you learned
You built a practical authentication schema with unique constraints, a CHECK constraint for roles, and an index for fast lookups — the same patterns used in real web applications.
Related Resources
Related Lessons
- Constraints
Learn about constraints.
- Indexes
Learn how indexes speed up lookups.
Related Glossary
- Index
Speeding up lookups.
- Unique Key
Preventing duplicates.
Related Glossary
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
- Index
Speeding up lookups.
- Unique Key
Preventing duplicates.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.