1.2 Creating a Blog Database

Let us try to understand SQL with an example of a blogging site in MySQL.

Here is how we can create a database named blog:

CREATE DATABASE blog;

Now, we will create a table named users in the blog database:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT(1) DEFAULT 0,
    INDEX email_idx (email)  -- Added index for email lookups
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 

Let us explain the above SQL query:

• CREATE TABLE is a SQL command used to create a new table.
• users is the name of the table.
• id, name, email, password, created_at, updated_at, and deleted are the columns in the table.
• INT, VARCHAR(50), TIMESTAMP, and BOOLEAN are the data types of the columns.
• charset=utf8 collate=utf8_general_ci specifies the character set and collation for the table.

Data type definitions:

• INT: Integer, can store whole numbers ranging from -2147483648 to 2147483647.

• VARCHAR(50): Variable-length character string with a maximum length of 50 characters in our case.

• UNIX_TIMESTAMP(): MySQL function that returns current Unix timestamp.

• TINYINT(1): A single-byte integer that is being used as a boolean since BOOLEAN is not supported in MySQL.

Constraints used in the query:

• PRIMARY KEY: A column or a group of columns that uniquely identifies each row in a table.
• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures that all values in a column are different.
• DEFAULT: Specifies a default value for a column when no value is specified.
• ON UPDATE: Sets the column to the current timestamp when the row is updated.

Let us now create a table named posts in the blog database:

CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT(1) DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,  -- Added ON DELETE
    INDEX user_id_idx (user_id)  -- Added index for foreign key
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Let us explain the above SQL query:

• CREATE TABLE is a SQL command used to create a new table.
• posts is the name of the table.
• id, user_id, title, body, created_at, updated_at, and deleted are the columns in the table.
• INT, VARCHAR(50), TEXT, and TIMESTAMP are the data types of the columns.

Data type definitions:

• INT: Refer to the explanation above.
• VARCHAR(50): Refer to the explanation above.
• TEXT: A string data type with a variable length. The maximum length is 65,535 characters.
• UNIX_TIMESTAMP(): Refer to the explanation above.
• TINYINT(1): Refer to the explanation above.
• FOREIGN KEY: A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

Constraints used in the query:

• PRIMARY KEY: Refer to the explanation above.
• NOT NULL: Refer to the explanation above.
• DEFAULT: Refer to the explanation above.
• ON UPDATE: Refer to the explanation above.
• REFERENCES: A constraint that defines a relationship between two tables.