Schema & Design
CREATE TABLE and ALTER TABLE
`CREATE TABLE` and `ALTER TABLE` are core SQL statements for defining and modifying the structure of relational tables.
Overview
CREATE TABLE and ALTER TABLE are core SQL statements for defining and modifying the structure of relational tables. CREATE TABLE was introduced in SQL-86 and remains the primary means of declaring a new base table, including its columns, data types, and constraints. ALTER TABLE was added in later revisions (SQL-92 and beyond) to allow schema evolution without dropping and recreating tables. Together they form the foundation of data definition language (DDL) in every major SQL database. CREATE TABLE specifies a table name, a list of column definitions, and optional table-level constraints such as primary keys, foreign keys, unique constraints, and check constraints. ALTER TABLE supports operations like adding columns, dropping columns, changing data types, adding or dropping constraints, and renaming tables or columns. Although the core syntax is standardized, vendor differences exist: for example, some systems require separate statements for adding constraints, while others allow multiple actions in a single ALTER TABLE. Understanding both statements is essential for schema design, migration, and maintenance. They are typically executed once during setup or during controlled schema changes, not in high-frequency transactional workloads.
Syntax and usage
The standard syntax for CREATE TABLE is: CREATE TABLE table_name ( column1 data_type [column_constraint], column2 data_type [column_constraint], ..., [table_constraint] );. Column constraints include NOT NULL, UNIQUE, PRIMARY KEY, REFERENCES, and CHECK. Table constraints can define composite primary keys, foreign keys, and unique constraints across multiple columns. The ALTER TABLE statement has several forms. To add a column: ALTER TABLE table_name ADD COLUMN column_name data_type;. To drop a column: ALTER TABLE table_name DROP COLUMN column_name;. To add a constraint: ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column);. To drop a constraint: ALTER TABLE table_name DROP CONSTRAINT constraint_name;. To change a column's data type: ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE new_type; (syntax varies). Many databases also support RENAME TABLE, RENAME COLUMN, and ADD PRIMARY KEY directly. Vendor differences are common: MySQL uses MODIFY COLUMN instead of ALTER COLUMN, PostgreSQL uses ALTER COLUMN ... TYPE, and SQL Server uses ALTER COLUMN column_name new_type. Always consult the target database's documentation for exact syntax.
How it works
When CREATE TABLE executes, the database engine parses the statement, validates the table and column names, resolves data types, and checks that referenced tables (for foreign keys) exist. It then allocates metadata entries in the system catalog and, depending on the storage engine, may create an empty physical file or segment. Constraints are recorded and enforced on subsequent data modifications. If the table already exists, most systems raise an error unless IF NOT EXISTS is specified (a common vendor extension). ALTER TABLE modifies the system catalog and may also rewrite existing data. Adding a nullable column with no default is typically a metadata-only change, but adding a column with a default or changing a data type can require a full table rewrite, which may lock the table and consume significant I/O. Dropping a column removes its metadata and may reclaim space, though some systems only mark it as dropped. Adding a foreign key constraint requires validating existing rows against the referenced table, which can be expensive. Transactional DDL support varies: PostgreSQL and SQL Server allow transactional DDL, while MySQL performs implicit commits. Understanding these mechanics helps predict locking, performance, and rollback behavior during schema changes.
Examples
Create a simple table with a primary key and a foreign key: CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT NOT NULL, order_date DATE NOT NULL, amount DECIMAL(10,2), CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );. Add a new column to an existing table: ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';. Add a unique constraint: ALTER TABLE orders ADD CONSTRAINT uq_order UNIQUE (order_id);. Change a column's data type (PostgreSQL syntax): ALTER TABLE orders ALTER COLUMN amount TYPE NUMERIC(12,2);. Drop a column: ALTER TABLE orders DROP COLUMN status;. Rename a table: ALTER TABLE orders RENAME TO customer_orders;. These examples illustrate typical DDL operations. Note that CREATE TABLE can also include CHECK constraints, such as CHECK (amount >= 0), and DEFAULT values. For composite primary keys, use a table-level constraint: PRIMARY KEY (order_id, line_number). Always test schema changes on a copy of production data before applying them to live systems.
Common pitfalls
One frequent mistake is assuming that ALTER TABLE is instantaneous. Adding a column with a default value can rewrite the entire table in some databases, causing downtime. Another pitfall is forgetting that dropping a column may break views, stored procedures, or application code that references it. Adding a NOT NULL column without a default fails if the table already contains rows, because existing rows would violate the constraint. Similarly, adding a foreign key constraint fails if existing data does not satisfy referential integrity. Data type changes can lead to implicit conversions, precision loss, or errors if the new type cannot accommodate existing values. Vendor-specific syntax is a common source of errors when migrating scripts between systems; for example, ALTER TABLE ... MODIFY COLUMN works in MySQL but not in PostgreSQL. Transactional behavior varies: in MySQL, DDL statements cause an implicit commit, so they cannot be rolled back. Locking is another concern: ALTER TABLE may acquire an exclusive lock, blocking reads and writes. Finally, using CREATE TABLE without IF NOT EXISTS can cause script failures on re-run. Always review the execution plan and test on a staging environment.
Best practices
Always define a primary key for every table to ensure row uniqueness and support efficient updates and deletes. Use explicit constraint names (e.g., CONSTRAINT fk_customer FOREIGN KEY ...) to simplify later ALTER TABLE ... DROP CONSTRAINT operations. Prefer adding columns as nullable or with a default that does not require a full rewrite; in large tables, consider adding the column without a default, then backfilling in batches, then adding the NOT NULL constraint. For data type changes, verify compatibility and test on a copy. Use IF NOT EXISTS and IF EXISTS where supported to make scripts idempotent. Avoid dropping columns directly; instead, rename them or mark them as deprecated, then drop after confirming no dependencies. When adding foreign keys, ensure the referenced table has a unique or primary key on the referenced columns. Document schema changes and use version-controlled migration tools. Monitor locking and performance during ALTER TABLE on production systems, and schedule such changes during low-traffic periods. Finally, keep DDL statements vendor-neutral where possible, but be prepared to adapt for specific databases.
Frequently asked questions
Q: Can I roll back a CREATE TABLE or ALTER TABLE? A: It depends on the database. PostgreSQL and SQL Server support transactional DDL, so you can roll back within a transaction. MySQL performs an implicit commit before and after DDL, so rollback is not possible; you must manually undo changes. Q: How do I add a column with a default value without locking the table? A: In some databases (e.g., PostgreSQL 11+), adding a column with a constant default is a metadata-only operation. In others, it may rewrite the table. Check your database's documentation; if a rewrite occurs, consider adding the column as nullable, then updating in batches, then setting the default and NOT NULL. Q: What is the difference between ALTER TABLE ... ADD CONSTRAINT and defining the constraint in CREATE TABLE? A: Functionally, both enforce the same rule. Adding a constraint later requires validating existing data, which can fail if the data violates the constraint. Defining it at creation time ensures the table never contains invalid data. For foreign keys, the referenced table must exist before you can add the constraint, so order matters.