CREATE TABLE in SQL

The CREATE TABLE statement in SQL is used to create a new table in a database. You define the table structure, including the column names, data types, and any constraints.

---

Syntax for CREATE TABLE

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

• table_name: The name of the table being created.

• column1, column2, ...: The names of the columns in the table.

• datatype: The data type of the column (e.g., INT, VARCHAR, DATE).

• constraint: Optional rules like PRIMARY KEY, NOT NULL, UNIQUE, etc.

---

Basic Example

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Age INT,
    Class VARCHAR(50)
);

This creates a table named Students with:

1. A StudentID column as the Primary Key.

2. A Name column that cannot be NULL.

3. Optional columns for Age and Class.

---

Variants of CREATE TABLE

1. Creating a Table with Constraints

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    DepartmentID INT UNIQUE,
    Salary DECIMAL(10, 2),
    JoiningDate DATE DEFAULT CURRENT_DATE
);

• UNIQUE: Ensures DepartmentID values are unique.

• DEFAULT: Sets the default value for JoiningDate as the current date.

2. Using FOREIGN KEY

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

This links the Orders table to the Customers table using CustomerID.

3. Using IF NOT EXISTS

To avoid errors if the table already exists:

CREATE TABLE IF NOT EXISTS Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

4. Temporary Tables

Temporary tables are only available during the session they are created in:

CREATE TEMPORARY TABLE TempOrders (
    OrderID INT,
    Amount DECIMAL(10, 2)
);

---

Explaining the Statement

How CREATE TABLE Works

1. Defines the Structure:
   Specifies how data is stored, its type, and constraints.

2. Ensures Data Integrity:
   With constraints like NOT NULL, PRIMARY KEY, FOREIGN KEY, etc., it ensures data validity.

3. Improves Query Optimization:
   Automatically creates indexes for primary and unique keys.

---

Common Constraints in CREATE TABLE

---

Example with Constraints

CREATE TABLE Books (
    BookID INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(200) NOT NULL,
    Author VARCHAR(100),
    PublishedYear YEAR CHECK (PublishedYear >= 1900),
    Genre VARCHAR(50),
    Price DECIMAL(8, 2) DEFAULT 0.00
);

This creates a Books table where:

• BookID auto-generates unique values.

• Title must have a value (not NULL).

• PublishedYear must be 1900 or later.

• Price defaults to 0.00 if not specified.

---

Common Errors and How to Avoid Them

1. Duplicate Table Name:
   Use IF NOT EXISTS to avoid errors when creating a table that already exists.

2. Mismatched Data Types:
   Ensure that the data types match the kind of data to be stored.

3. Missing Constraints:
   Properly define primary/foreign keys to maintain data integrity.

4. Forgetting DEFAULT Values:
   Use DEFAULT to ensure a column has a fallback value when not provided.