How do I change a column type in PostgreSQL?

Use ALTER TABLE ... ALTER COLUMN ... TYPE new_type. For example: ALTER TABLE users ALTER COLUMN name TYPE text;

How do I add a NOT NULL column in MySQL?

Add the column with a DEFAULT value first, then update existing rows, then drop the default or keep it: ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

How do I safely drop a column in SQL Server?

Check for dependencies (indexes, constraints, views) first, then use ALTER TABLE ... DROP COLUMN. Back up your data before dropping columns.

SQL Migration Recipes — Schema Change Patterns for PostgreSQL, MySQL & More

🔄 Change Column Type

Modify an existing column's data type. Common examples: VARCHAR(255) → TEXT, INT → BIGINT, NUMERIC → DECIMAL.

ALTER TABLE users
  ALTER COLUMN name TYPE text;

-- For type changes that require a cast:
ALTER TABLE products
  ALTER COLUMN price TYPE numeric(10,2)
  USING price::numeric(10,2);

ALTER TABLE users
  MODIFY COLUMN name TEXT;

-- Alternative syntax (MariaDB compatible):
ALTER TABLE products
  CHANGE COLUMN price price DECIMAL(10,2);

-- SQLite does not support ALTER COLUMN TYPE directly.
-- Recreate the table with the new type:
BEGIN TRANSACTION;
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  name TEXT  -- changed from VARCHAR(255)
);
INSERT INTO users_new SELECT * FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
COMMIT;

ALTER TABLE users
  ALTER COLUMN name NVARCHAR(500);

-- For size changes that affect indexes, drop and recreate:
ALTER TABLE products
  ALTER COLUMN price DECIMAL(10,2);

ALTER TABLE users
  MODIFY name VARCHAR2(4000);

-- With explicit cast if needed:
ALTER TABLE products
  MODIFY price NUMBER(10,2);

⚠ Safety Note: Changing a column type can truncate data (e.g., VARCHAR(100) → VARCHAR(50)) or cause implicit casting errors. Always back up your data and test on a staging database first.

Not sure what will break? Compare your schemas first. Try in SchemaLens →

➕ Add a NOT NULL Column

Add a required column to an existing table that already has rows. You must provide a default value for existing rows.

-- Step 1: Add column with default
ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

-- Step 2 (optional): Remove default if you want to enforce app-level inserts
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- MySQL requires a default for NOT NULL on existing tables
ALTER TABLE users
  ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

-- SQLite supports ADD COLUMN with default
ALTER TABLE users
  ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

ALTER TABLE users
  ADD status NVARCHAR(20) NOT NULL CONSTRAINT DF_users_status DEFAULT 'active';

-- Optional: drop the constraint later if you want to enforce app-level inserts
ALTER TABLE users DROP CONSTRAINT DF_users_status;

ALTER TABLE users
  ADD status VARCHAR2(20) DEFAULT 'active' NOT NULL;

⚠ Safety Note: On large tables, adding a NOT NULL column with a default can lock the table for a long time. For PostgreSQL 11+, this is optimized. For older versions or other databases, consider adding the column as NULL, backfilling data, then adding the constraint.

Verify the change across environments. Compare Schemas →

✏️ Rename a Column

Rename an existing column without changing its type or data. Update your application code to match.

ALTER TABLE users
  RENAME COLUMN full_name TO name;

ALTER TABLE users
  CHANGE COLUMN full_name name VARCHAR(255);

-- SQLite does not support RENAME COLUMN in older versions.
-- For SQLite 3.25.0+:
ALTER TABLE users RENAME COLUMN full_name TO name;

-- For older versions, recreate the table:

EXEC sp_rename 'users.full_name', 'name', 'COLUMN';

ALTER TABLE users
  RENAME COLUMN full_name TO name;

⚠ Safety Note: Renaming a column breaks views, stored procedures, triggers, and application code that reference the old name. Search your codebase thoroughly before deploying.

Find every reference before you rename. Compare Schemas →

🔗 Add a Foreign Key

Enforce referential integrity between two tables by adding a FOREIGN KEY constraint.

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

-- SQLite supports FK but requires it at table creation or via ALTER in 3.35.0+
-- For older versions, recreate the table with the FK.

-- SQLite 3.35.0+:
ALTER TABLE orders
  ADD COLUMN user_id INTEGER REFERENCES users(id);

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE;

⚠ Safety Note: Adding a foreign key on a large table can be slow and may lock both tables. Ensure existing data satisfies the constraint (no orphan rows) before adding the FK, or the statement will fail.

Spot missing constraints before they reach production. Compare Schemas →

🗑️ Drop a Column

Remove an unused column from a table. This is a destructive operation — use with caution.

ALTER TABLE users DROP COLUMN old_field;

ALTER TABLE users DROP COLUMN old_field;

-- SQLite does not support DROP COLUMN directly (until 3.35.0).
-- For older versions, recreate the table without the column.

-- SQLite 3.35.0+:
ALTER TABLE users DROP COLUMN old_field;

-- First drop any constraints/indexes referencing the column
ALTER TABLE users DROP CONSTRAINT CK_users_old_field;

-- Then drop the column
ALTER TABLE users DROP COLUMN old_field;

ALTER TABLE users DROP COLUMN old_field;

⚠ Safety Note: Dropping a column permanently deletes data. It can also break views, triggers, stored procedures, and application code. SchemaLens detects when a dropped column is referenced by a view and warns you before you deploy.

Check for view dependencies before dropping. Compare Schemas →

🔒 Add a Unique Constraint

Ensure no duplicate values exist for one or more columns by adding a UNIQUE constraint.

ALTER TABLE users
  ADD CONSTRAINT uq_users_email UNIQUE (email);

ALTER TABLE users
  ADD CONSTRAINT uq_users_email UNIQUE (email);

CREATE UNIQUE INDEX uq_users_email ON users(email);

ALTER TABLE users
  ADD CONSTRAINT uq_users_email UNIQUE (email);

ALTER TABLE users
  ADD CONSTRAINT uq_users_email UNIQUE (email);

⚠ Safety Note: Adding a UNIQUE constraint will fail if duplicate values already exist in the column. Clean your data first or use a partial/conditional unique index where supported.

Find duplicate data before adding constraints. Compare Schemas →

⚡ Add an Index

Speed up queries by adding an index on frequently filtered or joined columns.

CREATE INDEX idx_users_email ON users(email);

-- Partial index (PostgreSQL only):
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

CREATE INDEX idx_users_email ON users(email);

-- Composite index:
CREATE INDEX idx_users_name_status ON users(name, status);

CREATE INDEX idx_users_email ON users(email);

-- Covering index:
CREATE INDEX idx_users_email_name ON users(email, name);

CREATE INDEX idx_users_email ON users(email);

-- Include columns for covering index:
CREATE INDEX idx_users_email ON users(email) INCLUDE (name, created_at);

CREATE INDEX idx_users_email ON users(email);

-- Function-based index:
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

⚠ Safety Note: Adding an index on a large table can lock the table and consume significant disk I/O. Consider using CREATE INDEX CONCURRENTLY on PostgreSQL or online index builds on SQL Server to avoid blocking writes.

Compare indexes across environments. Compare Schemas →

⚙️ Change a Default Value

Update the default value for an existing column. This only affects future inserts, not existing rows.

ALTER TABLE users
  ALTER COLUMN status SET DEFAULT 'pending';

ALTER TABLE users
  ALTER COLUMN status SET DEFAULT 'pending';

-- SQLite does not support ALTER COLUMN.
-- Recreate the table or use a workaround with a new table.

-- Drop existing default constraint first (name may vary)
ALTER TABLE users DROP CONSTRAINT DF_users_status;

-- Add new default
ALTER TABLE users ADD CONSTRAINT DF_users_status DEFAULT 'pending' FOR status;

ALTER TABLE users
  MODIFY status DEFAULT 'pending';

⚠ Safety Note: Changing a default does not update existing rows. If you need to backfill existing data, run a separate UPDATE statement.

See every default change at a glance. Compare Schemas →

🔓 Make a Column Nullable

Remove the NOT NULL constraint from a column to allow NULL values.

ALTER TABLE users
  ALTER COLUMN name DROP NOT NULL;

ALTER TABLE users
  MODIFY COLUMN name VARCHAR(255) NULL;

-- SQLite does not support ALTER COLUMN.
-- Recreate the table with the column defined without NOT NULL.

ALTER TABLE users
  ALTER COLUMN name NVARCHAR(255) NULL;

ALTER TABLE users
  MODIFY name VARCHAR2(255) NULL;

⚠ Safety Note: Making a column nullable is generally safe, but your application code may now need to handle NULL values. Review ORM models, form validators, and API serializers.

Catch nullability drift between environments. Compare Schemas →

✅ Add a CHECK Constraint

Enforce data quality rules at the database level with a CHECK constraint.

ALTER TABLE products
  ADD CONSTRAINT chk_price_positive CHECK (price > 0);

-- Multi-column check:
ALTER TABLE events
  ADD CONSTRAINT chk_dates CHECK (end_date > start_date);

ALTER TABLE products
  ADD CONSTRAINT chk_price_positive CHECK (price > 0);

-- SQLite supports CHECK but cannot add it via ALTER TABLE.
-- Recreate the table with the CHECK constraint.

-- Or ensure the constraint is in your CREATE TABLE:

ALTER TABLE products
  ADD CONSTRAINT chk_price_positive CHECK (price > 0);

ALTER TABLE products
  ADD CONSTRAINT chk_price_positive CHECK (price > 0);

⚠ Safety Note: Adding a CHECK constraint on a large table may scan the entire table to validate existing rows. On PostgreSQL 12+, this is optimized with NOT VALID followed by VALIDATE CONSTRAINT.

Detect missing constraints across staging and prod. Compare Schemas →

SQL Migration Recipes

🔄 Change Column Type

➕ Add a NOT NULL Column

✏️ Rename a Column

🔗 Add a Foreign Key

🗑️ Drop a Column

🔒 Add a Unique Constraint

⚡ Add an Index

⚙️ Change a Default Value

🔓 Make a Column Nullable

✅ Add a CHECK Constraint

Deep-Dive Migration Guides

🔗 Add a Foreign Key

⚡ Create an Index

🗃️ SQLite ALTER TABLE

⏱️ Zero-Downtime Migration Guide