🛠️ SQL CREATE TABLE – Define a New Table Structure

🧲 Introduction – What is SQL CREATE TABLE?

The CREATE TABLE statement in SQL is used to define the structure of a new table in a database. It specifies the table name, column names, data types, and optional constraints like primary keys, uniqueness, or defaults.

🎯 In this guide, you’ll learn:

• Syntax of CREATE TABLE
• How to define columns and data types
• How to add constraints like PRIMARY KEY, UNIQUE, and DEFAULT
• Common best practices for schema design

---

✅ 1. Basic CREATE TABLE Syntax

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

✅ Defines the table and its columns in one command.

---

📋 2. Example – Create a Customers Table

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  created_at DATE DEFAULT CURRENT_DATE
);

✅ Includes PRIMARY KEY, UNIQUE, NOT NULL, and DEFAULT constraints.

---

🔐 3. Common Constraints

Constraint	Description
PRIMARY KEY	Uniquely identifies each row
NOT NULL	Column must have a value
UNIQUE	No duplicate values allowed
DEFAULT	Sets a default value if none is provided
CHECK	Ensures values meet a condition
FOREIGN KEY	Establishes a link to another table’s key

---

🧩 4. Example – Create Table with Foreign Key

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  amount DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

✅ Links orders.customer_id to customers.id.

---

🧱 5. Data Type Examples

Type	Description
INT	Integer numbers
VARCHAR(n)	Variable-length string
DATE	Calendar date
DECIMAL(x,y)	Fixed-point number
BOOLEAN	True or False (some DBMS)

---

📘 Best Practices

✅ Recommended	❌ Avoid This
Use clear, lowercase table/column names	Naming inconsistently
Include PRIMARY KEY in every table	Forgetting identifiers
Use appropriate data types	Overusing VARCHAR or TEXT
Add comments or documentation	Leaving schema unannotated

---

📌 Summary – Recap & Next Steps

CREATE TABLE is the foundation of SQL database design. It sets up how data is stored and validated, providing structure and consistency.

🔍 Key Takeaways:

• Use CREATE TABLE to define new table schemas
• Choose correct data types and apply constraints
• Always define primary keys for relational integrity

⚙️ Real-World Relevance:
Used in application setup scripts, ETL pipelines, schema migrations, and data warehousing.

➡️ Next: Learn ALTER TABLE to modify existing table structures.

---

❓ FAQ – SQL CREATE TABLE

❓ Can I create a table without a primary key?

✅ Yes, but it’s not recommended. Primary keys ensure row uniqueness.

❓ What is the difference between VARCHAR and TEXT?

✅ VARCHAR(n) limits length; TEXT allows unlimited text (often less index-friendly).

❓ Can I set default values?

✅ Yes. Use DEFAULT in column definitions.

❓ Can I define multiple constraints in one table?

✅ Yes. Constraints can be defined inline or at the bottom of the column list.

---

Share Now :