SQL

Table Operations

SQL · 10 entries

CREATE TABLE

Syntax

CREATE TABLE table_name (
  column_name data_type constraints,
  ...
);

Example

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 tabledefine tablenew tabletable definition

Common Data Types

Syntax

INTEGER, BIGINT, SMALLINT
DECIMAL(precision, scale), NUMERIC
VARCHAR(n), TEXT, CHAR(n)
BOOLEAN
DATE, TIME, TIMESTAMP, TIMESTAMPTZ
UUID, JSON, JSONB

Example

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.

primary keycomposite keytable keyunique identifier

FOREIGN KEY

Syntax

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.

foreign keyreference tablerelationshipreferential integrityon delete cascade

UNIQUE Constraint

Syntax

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.

unique constraintprevent duplicatesunique columnunique pair

CHECK Constraint

Syntax

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.

check constraintvalidate datacolumn validationenforce rule

DEFAULT Values

Syntax

column_name type DEFAULT value

Example

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 '%@%');

Output

-- Adds columns, modifies existing, adds constraint

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