Topic 4 of 52
CREATE TABLE
Overview
The CREATE TABLE command is the absolute foundation of Data Definition Language (DDL). It builds the physical blueprint for your data. When you execute this command, the database engine literally allocates space on the hard drive and sets up the structural metadata. A well-designed table defines columns, data types, and default values to prevent bad data from ever entering the system.
Syntax
sql
-- Basic CREATE TABLE syntax
CREATE TABLE users (
-- Column Name | Data Type | Optional Constraints
user_id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(255) UNIQUE,
age INT DEFAULT 18,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Creating a table ONLY if it doesn't already exist (Prevents crash errors)
CREATE TABLE IF NOT EXISTS departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100)
);Common Pitfalls
- Naming columns with SQL reserved keywords (like
select,table,order, oruser). If you name a columnorder, writingSELECT order FROM userswill instantly crash the parser. If you absolutely must use a keyword, you have to wrap it in backticks (MySQL) or double quotes (PostgreSQL):SELECT "order" FROM users. - Forgetting to set sane
DEFAULTvalues. If a user signs up but doesn't provide a profile picture, instead of letting it becomeNULL(which causes bugs in your frontend), defineDEFAULT 'default_avatar.png'.
Interview Questions
Q:
What happens if you run
CREATE TABLE users ... but a table named 'users' already exists in the database?A:
The database will instantly throw a fatal error and abort the transaction. To ensure idempotency (running the same script safely multiple times), you should use CREATE TABLE IF NOT EXISTS users ....
Real-World Example
Creating a robust 'Audit Log' table using automatic timestamps.
example
sql
CREATE TABLE security_audit_logs (
log_id BIGINT PRIMARY KEY,
action VARCHAR(50) NOT NULL,
ip_address VARCHAR(45), -- Supports IPv6 length
-- Let the database automatically tag the exact microsecond it happened!
-- The backend code never has to pass this timestamp manually.
occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Check Your Knowledge
Test your understanding of CREATE TABLE with these quick questions.