🧱 SQL `CREATE TABLE` Statement – How to Create a New Table in SQL

The CREATE TABLE command in SQL is used to define a new table in your database, specifying its name, columns, and data types.

This is one of the most important SQL commands, forming the foundation for storing structured data in relational databases like MySQL, SQL Server, PostgreSQL, and Oracle.

📘 What is SQL `CREATE TABLE`?

The CREATE TABLE statement is used to create a new database table with specified columns, their data types, and optional constraints (like PRIMARY KEY, NOT NULL, etc.).

You can use it to design a schema that reflects your business logic.

🧾 SQL `CREATE TABLE` Syntax

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

🧩 Common Data Types

Data Type	Description
`INT`	Integer (whole number)
`VARCHAR(n)`	Variable-length string (e.g., `VARCHAR(100)`)
`DATE`	Date (YYYY-MM-DD)
`DECIMAL(x,y)`	Decimal with x digits and y decimals
`BOOLEAN`	True or False

✅ Example: Create a `customers` Table

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100),
  signup_date DATE
);

This creates a table named customers with:

A customer_id as the primary key
First and last names (required)
An optional email
A signup date

🧠 Best Practices for Creating Tables

Always define a primary key
Use appropriate data types to save storage and ensure accuracy
Apply NOT NULL constraints to critical columns
Consider naming conventions for clarity and consistency
Plan for indexes and foreign keys (if joining other tables)

⚠️ Common Errors to Avoid

Mistake	Tip to Fix
Forgetting data types	Every column must have a defined type
Duplicate column names	Each column name must be unique
Invalid constraint placement	Use proper syntax for `PRIMARY KEY`, `UNIQUE`, etc.
Missing commas between columns	Ensure every column line ends with a comma (except the last one)

🚀 Creating a Table with Constraints

You can add constraints like:

PRIMARY KEY – Unique, not null identifier
UNIQUE – Prevent duplicate values
NOT NULL – Mandatory value
CHECK – Conditional validation
FOREIGN KEY – Relationship with another table

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE NOT NULL,
  amount DECIMAL(10,2) CHECK (amount > 0),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

📝 Summary

The CREATE TABLE statement lets you define a new table in the database
Specify each column name, data type, and optional constraints
Use constraints (PRIMARY KEY, NOT NULL, etc.) to enforce data integrity
A well-structured table leads to scalable and efficient databases

CREATE TABLE Statement

🧱 SQL CREATE TABLE Statement – How to Create a New Table in SQL

📘 What is SQL CREATE TABLE?

🧾 SQL CREATE TABLE Syntax