🧱 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