Home / SQL / CREATE TABLE

CREATE TABLE

Overview

The CREATE TABLE statement is used to create a new table in a database. A table consists of columns (attributes) and rows (records), where each column has a data type and optionally constraints.

Tables are the fundamental storage structures in relational databases, holding all your data in an organized format.

Basic Syntax

CREATE TABLE Syntax

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ...
);

Examples

Example 1: Simple Table

Create a basic table

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20)
);

Example 2: Table with Multiple Constraints

Table with various constraints

CREATE TABLE Products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    category VARCHAR(50),
    stock_quantity INT DEFAULT 0,
    created_date DATE DEFAULT CURRENT_DATE
);

Example 3: Table with Foreign Key

Table with relationships

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Example 4: Table with Composite Primary Key

Multiple columns as primary key

CREATE TABLE OrderItems (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

Column Definitions

Each column in a table must have:

Column name: Identifier for the column
Data type: Type of data the column can store (see Data Types)
Optional constraints: Rules for the column (see Constraints)

Common Data Types

Basic data types used in table creation:

Data Type	Description	Example
`INT`	Integer numbers	`customer_id INT`
`VARCHAR(n)`	Variable-length string	`name VARCHAR(100)`
`DECIMAL(p,s)`	Fixed-point numbers	`price DECIMAL(10,2)`
`DATE`	Date values	`order_date DATE`
`BOOLEAN`	True/false values	`is_active BOOLEAN`

Table Constraints

Constraints define rules for table data (covered in detail in Constraints tutorial):

PRIMARY KEY: Uniquely identifies each row
FOREIGN KEY: Links to another table
NOT NULL: Column cannot be empty
UNIQUE: Column values must be unique
CHECK: Validates column values
DEFAULT: Sets default value for column

Table Naming Conventions

Best practices for naming tables:

Use plural nouns: Tables represent collections (Customers, Orders)
Use lowercase: Or camelCase consistently
Be descriptive: Name reflects table purpose
Avoid reserved words: Don't use SQL keywords
Keep it concise: But meaningful

Creating Table from Existing Table

You can create a new table from an existing table's structure and/or data:

Copy Structure Only

Copy structure without data

CREATE TABLE Customers_backup LIKE Customers;

Copy Structure and Data

Copy table with data

CREATE TABLE Customers_backup AS 
SELECT * FROM Customers;

Copy with Selected Columns

Copy specific columns

CREATE TABLE Customers_summary AS 
SELECT customer_id, name, email 
FROM Customers;

Table Design Best Practices

Always define a primary key: Ensures uniqueness
Use appropriate data types: Match data requirements
Set NOT NULL when needed: Enforce required fields
Use foreign keys: Maintain referential integrity
Add indexes: Improve query performance
Normalize structure: Follow normalization rules

Common Errors

Table already exists: Use IF NOT EXISTS or DROP first
Invalid column name: Contains special characters or reserved words
Missing data type: Every column needs a data type
Foreign key mismatch: Referenced column must exist
Invalid constraint: Constraint syntax incorrect

Best Practices

Plan design first: Design tables before creating them
Use meaningful names: Make purpose clear
Define constraints: Enforce data integrity
Document structure: Comment complex tables
Test thoroughly: Verify table structure is correct
Version control: Track table creation scripts

Next Steps

Learn about modifying tables with ALTER TABLE, or explore table constraints in Constraints.

← Drop Db Alter Table →