My Web Training

📖 MySQL Create a Database and Tables

Creating a Database

A database is a collection of related tables. Each database has a unique name within the MySQL server instance. When creating a database, choose a name that reflects its purpose and adheres to naming conventions (e.g., no spaces, starts with a letter, uses underscores instead of special characters).

Add a Database

CREATE DATABASE [IF NOT EXISTS] db_name;

Delete a Database

DROP DATABASE [IF EXISTS] db_name;

Select or Use a Database

USE db_name;

Note: MySQL does not support USE DATABASE. Use USE db_name; without the word DATABASE.

Creating a Table Within a Database

When creating a table you specify the table name, the column names for the table, the data types for each column, and any attributes you might assign to a specific column.

Example

CREATE TABLE student (
    id INT AUTO_INCREMENT,
    student_name VARCHAR(75) NOT NULL UNIQUE,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

In the example above, id is the primary key and auto-increments with each new row. The student_name field must be unique and not null. The ENGINE=InnoDB clause ensures that the table supports transactions and foreign key constraints.

Schema Design Tips

• Always define a primary key for each table
• Use NOT NULL unless a field should accept blank values
• Choose VARCHAR lengths based on realistic input, not maximums
• Use DECIMAL for currency, not FLOAT or DOUBLE
• Use ENGINE=InnoDB to support foreign keys and transactions

Common Column Attributes

NOT NULL

When inserting rows in the table the column must contain data.

UNIQUE

The value stored in the column must be different from any other row's value in that column. MySQL will automatically create an index when you apply UNIQUE.

DEFAULT

If no value is passed to the column, assign a default value.

AUTO_INCREMENT

Generates a unique number automatically. Often used with primary keys. Only one column per table can use this attribute.

Table and Column Constraints

Primary Key Constraint

A primary key is a unique identifier for each record. It may be defined inline or as a separate constraint.

Foreign Key Constraint

Used on the many side of a one-to-many relationship to define the relationship between tables and to enforce referential integrity.

Referential Integrity

Prevents inserting a value in a foreign key column unless a matching record exists in the parent table.

Prevents deleting a record from the parent table if related rows still exist in the child table.

Important: In MySQL, referential integrity is only enforced if the table uses the InnoDB storage engine.

Example: Basic Foreign Key Constraint

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2) NOT NULL,
    CONSTRAINT fk_customer FOREIGN KEY (customer_id)
        REFERENCES customer(id)
) ENGINE=InnoDB;

Clarifying Inline REFERENCES Shortcuts

MySQL also supports a shorthand:

customer_id INT REFERENCES customer(id)

However, this may not fully enforce referential integrity unless MySQL recognizes it within a full FOREIGN KEY clause. The preferred style is to explicitly use CONSTRAINT ... FOREIGN KEY for clarity and reliability.

ON DELETE CASCADE Clause

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2) NOT NULL,
    CONSTRAINT orders_fk_customer FOREIGN KEY (customer_id)
        REFERENCES customer(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

This allows automatic cleanup of related orders when a customer is removed. Use with caution — this can result in unintended data loss if misapplied.

Character Sets and Collations

Character sets determine which characters are stored and how they're encoded. Collation defines how characters are sorted and compared.

• latin1 – legacy default in older MySQL versions; supports Western European characters only
• utf8 – supports many Unicode characters, but is limited to 3 bytes per character
• utf8mb4 – full Unicode support; required for emoji, multi-language, and modern web apps

Warning: utf8 in MySQL does not support the full Unicode range. Always use utf8mb4 for new tables and databases.

To check or display character settings:

SHOW CHARSET;
SHOW COLLATION;
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

Collation Suffix Reference

Suffix	Meaning
_ai	Accent-insensitive
_as	Accent-sensitive
_ci	Case-insensitive
_cs	Case-sensitive
_ks	Kana-sensitive
_bin	Binary

Storage Engines

The storage engine defines how tables are stored and managed on disk. The two most common engines:

InnoDB

Default engine in MySQL 5.5 and later. Supports transactions and foreign key constraints.

MyISAM

Used in older versions. Does not support transactions or foreign keys.

Check Supported Engines

SHOW ENGINES;

Table Status

The SHOW TABLE STATUS command displays detailed metadata about your tables, including storage engine, collation, row count, and more.

Syntax

SHOW TABLE STATUS
    [FROM db_name]
    [LIKE 'pattern'];

Note: The optional LIKE clause accepts SQL wildcard patterns, not just table names. Use % to match multiple characters or _ for a single character.

Example

SHOW TABLE STATUS
    FROM `classdb`
    LIKE 'student';

Suggested Naming Conventions and Design Practices

To ensure consistency and readability across all database projects at DataForge Systems, we recommend the following naming conventions and design practices. These align with industry standards, reduce ambiguity, and make your models easier to maintain over time.

Table Names

• Use singular nouns (e.g., student, course, order)
• Use lowercase letters and snake_case formatting
• Avoid abbreviations unless commonly understood

Primary Keys

• Use a consistent name of id for all primary key columns
• Use AUTO_INCREMENT for numeric surrogate keys unless a natural key is preferred

Foreign Keys

• Name foreign key fields using the pattern referencedtable_id (e.g., student_id)
• Always define a foreign key constraint for integrity

Column Names

• Use snake_case consistently (e.g., first_name, created_at)
• Be descriptive but concise
• Avoid MySQL reserved words as column names

Other Practices

• Use ENGINE=InnoDB on all tables
• Use utf8mb4 character set with utf8mb4_0900_ai_ci collation
• Use DECIMAL for currency values

Example Schema

CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE course (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE enrollment (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES student(id),
    CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES course(id)
) ENGINE=InnoDB;

Practice Expectations

• Use id for all primary key fields
• Use tablename_id format for foreign keys
• Use singular, lowercase, snake_case naming
• Explicitly define PKs, FKs, and storage engine

Additional Resources

• MySQL 8.0 Reference: CREATE TABLE
• MySQL 8.0: Foreign Key Constraints
• MySQL 8.0: Character Sets and Collations
• MySQL 8.0: SHOW TABLE STATUS
• MySQL 8.0: Server SQL Modes

Last updated: September 12, 2025 at 8:14 PM