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:

A StudentID column as the Primary Key.
A Name column that cannot be NULL.
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

Defines the Structure:
Specifies how data is stored, its type, and constraints.
Ensures Data Integrity:
With constraints like NOT NULL, PRIMARY KEY, FOREIGN KEY, etc., it ensures data validity.
Improves Query Optimization:
Automatically creates indexes for primary and unique keys.

Common Constraints in CREATE TABLE

Constraint	Description
`PRIMARY KEY`	Uniquely identifies each row in the table.
`FOREIGN KEY`	Links the column to a primary key in another table.
`NOT NULL`	Ensures the column cannot have `NULL` values.
`UNIQUE`	Ensures all values in the column are unique.
`DEFAULT`	Sets a default value for the column if no value is specified.
`CHECK`	Ensures a column satisfies a specific condition.
`AUTO_INCREMENT`	Automatically generates unique values for the column (MySQL).

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

Duplicate Table Name:
Use IF NOT EXISTS to avoid errors when creating a table that already exists.
Mismatched Data Types:
Ensure that the data types match the kind of data to be stored.
Missing Constraints:
Properly define primary/foreign keys to maintain data integrity.
Forgetting DEFAULT Values:
Use DEFAULT to ensure a column has a fallback value when not provided.