CREATE TABLE

The cornerstone of DDL, CREATE TABLE defines a new table, specifying its name, the columns it contains, the data type for each column, and any initial constraints.

-- Creating the Users table for TokTuk
CREATE TABLE Users (
    user_id INTEGER PRIMARY KEY, -- Unique identifier for each user
    username TEXT NOT NULL UNIQUE, -- Username must be provided and unique
    email TEXT NOT NULL UNIQUE, -- Email must be provided and unique
    join_date DATE DEFAULT CURRENT_DATE, -- Records signup date, defaults to today
    country TEXT,
    followers_count INTEGER DEFAULT 0 -- Must be non-negative
);

This command creates the Users table with various columns and constraints like PRIMARY KEY, NOT NULL, UNIQUE, and DEFAULT.

ALTER TABLE

Databases evolve. ALTER TABLE allows you to modify an existing table's structure. You can add, drop, or modify columns, add or drop constraints, or rename the table itself.

-- Adding a 'last_login' timestamp column to Users
ALTER TABLE Users
ADD COLUMN last_login TIMESTAMP;

-- Renaming the 'country' column to 'location'
ALTER TABLE Users
RENAME COLUMN country TO location;

-- Dropping the 'followers_count' column (Use with caution!)
ALTER TABLE Users
DROP COLUMN followers_count;

ALTER TABLE provides the flexibility to adapt your schema as application requirements change, without needing to drop and recreate tables. Syntax can vary slightly between database systems (e.g., MySQL, PostgreSQL, SQL Server).

DROP TABLE

The DROP TABLE command permanently deletes a table and all the data stored within it. This action is irreversible, so it must be used with extreme caution, especially in production environments.

-- Removing a temporary or obsolete table
DROP TABLE temp_user_data;

-- Using IF EXISTS to avoid an error if the table is already gone
DROP TABLE IF EXISTS old_feature_flags;

Always double-check the table name and ensure you have backups before executing DROP TABLE. Dependencies (like foreign keys referencing the table) might prevent the drop or require cascading options.

PRIMARY KEY

A PRIMARY KEY constraint uniquely identifies each record in a table. It ensures entity integrity – no two rows can have the same primary key value, and the primary key column(s) cannot contain NULL values. Tables typically have one primary key.

-- Defining during table creation (single column)
CREATE TABLE Videos (
    video_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
    -- ... other columns
);

-- Defining during table creation (composite key)
CREATE TABLE Likes (
    user_id INTEGER,
    video_id INTEGER,
    like_time TIMESTAMP,
    PRIMARY KEY (user_id, video_id) -- Unique combination identifies a like
);

-- Adding after table creation
ALTER TABLE Users
ADD CONSTRAINT pk_users PRIMARY KEY (user_id);

Primary keys are fundamental for relating tables and are often automatically indexed for fast lookups.

FOREIGN KEY

A FOREIGN KEY constraint establishes a link between two tables, enforcing referential integrity. It ensures that a value in a column (or columns) of one table must match a value in the primary key column(s) of another table. This prevents "orphan" records.

-- Defining during table creation
CREATE TABLE Videos (
    video_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL, -- Column referencing the Users table
    title TEXT,
    -- ... other columns
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
        ON DELETE CASCADE -- Optional: If a user is deleted, delete their videos too
        ON UPDATE NO ACTION -- Optional: If user_id changes, do nothing (often default)
);

-- Adding after table creation
ALTER TABLE Comments
ADD CONSTRAINT fk_comments_video
FOREIGN KEY (video_id) REFERENCES Videos(video_id);

Foreign keys are the backbone of relational database design, defining how data entities connect. Actions like ON DELETE and ON UPDATE specify how changes in the referenced (parent) table affect the referencing (child) table.

CONSTRAINTS

Constraints are rules enforced on data columns to ensure data accuracy and reliability. Besides PRIMARY KEY and FOREIGN KEY, common constraints include:

• NOT NULL: Ensures a column cannot have a NULL value.
• UNIQUE: Ensures all values in a column (or set of columns) are distinct.
• DEFAULT: Provides a default value for a column when none is specified during insertion.

CREATE TABLE Products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL UNIQUE,
    price REAL NOT NULL, -- Price must be positive
    stock_quantity INTEGER DEFAULT 0, -- Defaults to 0 if not specified
    category TEXT
);

Constraints are vital for maintaining data integrity at the database level, preventing invalid data from being entered.