CREATE TABLE users (
id SERIAL PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
output
-- Table created with auto-increment ID, constraints, and defaults
Note SERIAL is PostgreSQL-specific (auto-increment integer). MySQL uses INT AUTO_INCREMENT. Standard SQL uses GENERATED ALWAYS AS IDENTITY. Always define a primary key.
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
weight_kg NUMERIC(6, 3),
description TEXT,
is_available BOOLEAN DEFAULT true,
metadata JSONB,
created_at TIMESTAMPTZ DEFAULT NOW()
);
output
-- Demonstrates typical column type choices
Note Use DECIMAL for money — never FLOAT or DOUBLE, which have rounding errors. Use TEXT over VARCHAR when you do not need a length limit. JSONB (PostgreSQL) is preferred over JSON because it supports indexing.
data typescolumn typesvarchar vs textinteger typesdecimal vs float
PRIMARY KEY
syntax
column_name type PRIMARY KEY
-- or composite:
PRIMARY KEY (col1, col2)
example
CREATE TABLE order_items (
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1,
unit_price DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);
output
-- Composite primary key on (order_id, product_id)
Note A primary key is automatically NOT NULL and UNIQUE. Composite primary keys are useful for junction/bridge tables. Each table should have exactly one primary key.
FOREIGN KEY (column) REFERENCES other_table(column)
ON DELETE action ON UPDATE action
example
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
total_amount DECIMAL(10, 2),
order_date DATE DEFAULT CURRENT_DATE,
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
output
-- user_id must reference an existing users.id
Note ON DELETE options: RESTRICT (block), CASCADE (delete child rows), SET NULL, SET DEFAULT. CASCADE is convenient but dangerous — one delete can wipe many related rows. Default is RESTRICT in most databases.
column_name type UNIQUE
-- or table-level:
UNIQUE (col1, col2)
example
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
employee_number VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL,
department_id INTEGER NOT NULL,
UNIQUE (email, department_id)
);
output
-- employee_number is unique on its own;
-- (email, department_id) must be unique as a pair
Note UNIQUE allows multiple NULLs in most databases (PostgreSQL, MySQL). SQL Server treats NULLs as equal in unique constraints by default, so only one NULL is allowed. Use a partial unique index to handle this.
column_name type CHECK (condition)
-- or table-level:
CHECK (condition involving multiple columns)
example
CREATE TABLE events (
id SERIAL PRIMARY KEY,
event_name VARCHAR(200) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
max_attendees INTEGER CHECK (max_attendees > 0),
CHECK (end_date >= start_date)
);
output
-- Ensures max_attendees is positive and end_date is not before start_date
Note MySQL 8.0+ supports CHECK constraints (earlier versions parsed but silently ignored them). CHECK constraints cannot reference other tables — use triggers or application logic for cross-table validation.
CREATE TABLE tasks (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
priority INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
output
-- Omitting status, priority, or created_at uses defaults
Note DEFAULT applies only when the column is omitted from INSERT. Explicitly inserting NULL overrides the default with NULL (unless NOT NULL is also set). You can use expressions like CURRENT_TIMESTAMP as defaults.
default valuecolumn defaultauto fill column
ALTER TABLE
syntax
ALTER TABLE table_name
ADD COLUMN col type constraints,
DROP COLUMN col,
ALTER COLUMN col SET DATA TYPE new_type,
ADD CONSTRAINT name constraint_definition;
example
ALTER TABLE users
ADD COLUMN phone VARCHAR(20),
ADD COLUMN updated_at TIMESTAMP;
ALTER TABLE users
ALTER COLUMN email SET NOT NULL;
ALTER TABLE users
ADD CONSTRAINT chk_email CHECK (email LIKE '%@%');
Note ALTER TABLE syntax varies across databases. PostgreSQL uses ALTER COLUMN ... TYPE. MySQL uses MODIFY COLUMN. Adding NOT NULL to a column with existing NULLs will fail — update the data first.
alter tableadd columndrop columnmodify columnchange tablerename column
DROP TABLE
syntax
DROP TABLE table_name;
DROP TABLE IF EXISTS table_name CASCADE;
example
DROP TABLE IF EXISTS temp_import;
-- PostgreSQL: CASCADE drops dependent objects
DROP TABLE IF EXISTS categories CASCADE;
output
-- Table and its data are permanently removed
Note DROP TABLE is irreversible outside of a transaction. CASCADE also drops views, foreign keys, and other objects that depend on the table. MySQL does not support CASCADE on DROP TABLE the same way.
drop tabledelete tableremove tabledrop if exists
CREATE TABLE IF NOT EXISTS
syntax
CREATE TABLE IF NOT EXISTS table_name (
columns...
);
example
CREATE TABLE IF NOT EXISTS audit_log (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
table_name VARCHAR(100) NOT NULL,
action VARCHAR(10) NOT NULL,
old_data JSONB,
new_data JSONB,
changed_by INTEGER,
changed_at TIMESTAMPTZ DEFAULT NOW()
);
output
-- Creates the table only if it does not already exist
Note IF NOT EXISTS prevents errors in migration scripts that might run multiple times. It does NOT verify that the existing table has the same schema — it just skips creation if the name exists.
create if not existsidempotent createsafe create table