SQL CREATE TABLE statement
Here is a clear, complete, and practical guide to the SQL CREATE TABLE statement, with examples for MySQL, PostgreSQL, SQL Server, and Oracle.
What SQL CREATE TABLE statement Does
CREATE TABLE creates a new table in a database with defined:
Columns
Data types
Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, etc.)
Default values
Basic ANSI SQL Syntax
Simple Example
Example With Constraints
Example With FOREIGN KEY
MySQL — CREATE TABLE Example
PostgreSQL — CREATE TABLE Example
SQL Server — CREATE TABLE Example
Oracle — CREATE TABLE Example
Add a CHECK Constraint
Common SQL Data Types
| Type | Description |
|---|---|
| INT / BIGINT | Whole numbers |
| VARCHAR(n) | Text up to n chars |
| TEXT | Long text |
| DATE | Date only |
| TIMESTAMP / DATETIME | Date + time |
| DECIMAL(p,s) | Precise numbers (money) |
| BOOLEAN | True/false |
| SERIAL (PostgreSQL) | Auto-increment integer |
| AUTO_INCREMENT (MySQL) | Auto-increment integer |
| IDENTITY (SQL Server) | Auto-increment integer |
Best Practices
- Always define a PRIMARY KEY
- Use meaningful column names
- Use appropriate data types (especially for money:
DECIMAL) - Use
NOT NULLwhere required - Add indexes for frequent search columns
- Use foreign keys to maintain data integrity
- Use snake_case or lower_case naming conventions
- Avoid unnecessary large text fields (
TEXT,LONGTEXT)
