SQL ALTER TABLE

SQL
POSTGRES
INTERACTIVE

Complete SQL ALTER TABLE Technical Guide

1. Fundamental Syntax 
ALTER TABLE table_name
[alter_action] [alter_action ...];
  1. Single or multiple actions per statement (varies by database)
  2. Schema modification vs data modification behaviors
  3. Locking implications: Some operations require table locks
2. Column Operations
Adding Columns
ALTER TABLE employees
ADD COLUMN middle_name VARCHAR(50),
ADD COLUMN hire_date DATE DEFAULT CURRENT_DATE;
Modifying Columns
-- PostgreSQL/SQL Server
ALTER TABLE products
ALTER COLUMN price TYPE DECIMAL(10,2),
ALTER COLUMN description SET NOT NULL;

-- MySQL
ALTER TABLE products
MODIFY COLUMN price DECIMAL(10,2) NOT NULL;
Dropping Columns
ALTER TABLE customers
DROP COLUMN legacy_id,
DROP COLUMN obsolete_flag;
3. Constraint Management
Adding Constraints
ALTER TABLE orders
ADD PRIMARY KEY (order_id),
ADD CONSTRAINT fk_customer 
  FOREIGN KEY (customer_id) REFERENCES customers(id),
ADD CONSTRAINT chk_amount 
  CHECK (amount > 0);
Dropping Constraints
ALTER TABLE orders
DROP CONSTRAINT chk_amount,
DROP CONSTRAINT fk_customer;
4. Performance-Critical Operations
Index Management
-- Adding indexes
ALTER TABLE products
ADD INDEX idx_name (product_name),
ADD UNIQUE INDEX idx_sku (sku);

-- Dropping indexes
ALTER TABLE products
DROP INDEX idx_name;
Table Restructuring
-- MySQL table rebuild
ALTER TABLE large_data ENGINE=InnoDB, ROW_FORMAT=COMPRESSED;

-- PostgreSQL storage parameters
ALTER TABLE logs SET (fillfactor = 70);
5. Database-Specific Features
PostgreSQL
-- Add column with non-atomic default
ALTER TABLE users
ADD COLUMN preferences JSONB,
ALTER COLUMN preferences SET DEFAULT '{}'::jsonb;

-- Change column statistics
ALTER TABLE measurements
ALTER COLUMN sensor_value SET STATISTICS 1000;
SQL Server
-- Online index rebuild
ALTER INDEX idx_customer ON orders REBUILD WITH (ONLINE = ON);

-- Filegroup movement
ALTER TABLE archive_data REBUILD WITH (DATA_COMPRESSION = PAGE);
Oracle
-- Invisible columns
ALTER TABLE employees
MODIFY (salary INVISIBLE);

-- Shrink space
ALTER TABLE orders ENABLE ROW MOVEMENT;
ALTER TABLE orders SHRINK SPACE COMPACT;
6. Advanced Scenarios
Partition Management
-- PostgreSQL partition attach
ALTER TABLE sales ATTACH PARTITION sales_q1
FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

-- MySQL partition reorganization
ALTER TABLE logs
REORGANIZE PARTITION p0 INTO (
  PARTITION p0 VALUES LESS THAN (1000),
  PARTITION p1 VALUES LESS THAN (2000)
);
Generated Columns
ALTER TABLE products
ADD COLUMN price_with_tax DECIMAL(10,2)
GENERATED ALWAYS AS (price * 1.1) STORED;
Temporal Tables
-- SQL Server system-versioning
ALTER TABLE employees
ADD PERIOD FOR SYSTEM_TIME (valid_from, valid_to),
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.employees_history));

Advanced ALTER TABLE Topics

1. Metadata Locking Behavior 
# MySQL lock monitoring
SHOW PROCESSLIST;
SELECT * FROM performance_schema.metadata_locks;

# PostgreSQL lock checking
SELECT locktype, mode, relation::regclass 
FROM pg_locks WHERE relation = 'table_name'::regclass;
  1. Exclusive DDL locks: Block all concurrent access during schema changes
  2. Lock escalation: Some operations upgrade row locks to table locks
  3. Deadlock potential: With multiple concurrent DDL statements
  4. Duration: Locks held until transaction completion
2. Online Schema Change Techniques 
-- MySQL 8.0+ online DDL
ALTER TABLE customers 
ADD COLUMN loyalty_score INT,
ALGORITHM=INPLACE, LOCK=NONE;

-- PostgreSQL concurrent index
CREATE INDEX CONCURRENTLY idx_customer_name ON customers(name);

-- SQL Server online index rebuild
ALTER INDEX idx_orders ON orders REBUILD WITH (ONLINE = ON);
  1. In-Place vs Copy: Algorithms that avoid table rebuilds
  2. Minimal Locking: LOCK=NONE vs LOCK=SHARED modes
  3. Progress Monitoring: Tracking long-running operations
  4. Limitations: Operations that can't be performed online
3. Schema Migration Patterns 
-- Flyway migration example
-- V2__Add_email_verification.sql
ALTER TABLE users 
ADD COLUMN email_verified BOOLEAN DEFAULT false,
ADD COLUMN verification_token VARCHAR(100);

-- Liquibase changeset
<changeSet author="dev" id="add-payment-columns">
  <addColumn tableName="orders">
    <column name="payment_method" type="VARCHAR(50)"/>
    <column name="payment_id" type="UUID"/>
  </addColumn>
</changeSet>
  1. Version control: Managing changes across environments
  2. Rollback strategies: Reversible migrations
  3. Zero-downtime deploys: Blue-green schema changes
  4. State-based vs migration-based: Different tooling approaches
4. System Catalog Changes 
-- PostgreSQL catalog impact
SELECT relname, relfilenode, reltuples 
FROM pg_class WHERE relname = 'modified_table';

-- SQL Server metadata queries
SELECT OBJECT_NAME(object_id), * 
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('schema.changed_table');
  1. OID reassignment: Some ALTERs change internal object identifiers
  2. Statistics reset: How column changes affect query planning
  3. Dependency tracking: Views/stored procedures depending on modified tables
  4. Catalog bloat: Accumulation of metadata from frequent DDL
5. Cross-Database Syntax Differences 
/* Renaming columns across databases */
-- MySQL
ALTER TABLE products CHANGE COLUMN prod_name product_name VARCHAR(100);

-- PostgreSQL
ALTER TABLE products RENAME COLUMN prod_name TO product_name;

-- SQL Server
EXEC sp_rename 'products.prod_name', 'product_name', 'COLUMN';

-- Oracle
ALTER TABLE products RENAME COLUMN prod_name TO product_name;
  1. Data type changes: Different syntax for column type modifications
  2. Default values: Varying handling of existing data during changes
  3. Atomic operations: Which databases support multiple changes in one statement
  4. Constraint management: Different approaches to constraint modification
6. Replication and ALTER TABLE 
# MySQL replication filters
[mysqld]
replicate-wild-ignore-table=db.temp_%

# PostgreSQL logical decoding
CREATE PUBLICATION alter_monitor 
FOR TABLE important_table 
WITH (publish = 'ddl');

# SQL Server CDC configuration
EXEC sys.sp_cdc_enable_table
  @source_schema = 'dbo',
  @source_name = 'Customer',
  @role_name = NULL,
  @supports_net_changes = 1;
  1. Statement vs row-based: How different replication modes handle DDL
  2. Schema change propagation: Timing and reliability across nodes
  3. Multi-master conflicts: Handling concurrent DDL in different regions
  4. Temporary tables: Special replication considerations