MySQL Database Structure, Constraints, Views and Security

MySQL is not only used to store data. It is also used to design database structures, define relationships, protect data integrity, improve query performance, and secure applications from dangerous attacks such as SQL injection.

After learning basic SQL commands such as SELECT, WHERE, INSERT, UPDATE, DELETE, JOIN, GROUP BY, and HAVING, the next important step is understanding how databases and tables are created and controlled. This includes creating databases, creating tables, defining constraints, using indexes, handling dates, creating views, and writing secure queries with prepared statements.

This article explains the MySQL topics shown in the learning path: Create DB, Drop DB, Create Table, Drop Table, Alter Table, Constraints, NOT NULL, UNIQUE, Primary Key, Foreign Key, CHECK, DEFAULT, Create Index, Auto Increment, Dates, Views, SQL Injection, and Prepared Statements.

MySQL Create DB

Creating a database is usually the first step in building a MySQL project. A database is a container that holds tables, views, indexes, relationships, and other database objects.

In MySQL, a database can be created using the CREATE DATABASE statement.

CREATE DATABASE company_db;

This command creates a new database named company_db. After creating the database, you need to select it before creating tables inside it.

USE company_db;

In real applications, database names should be clear and meaningful. For example, names such as school_db, ecommerce_db, blog_db, or inventory_db are easier to understand than random names.

You can also create a database only if it does not already exist. This helps avoid errors when running setup scripts multiple times.

CREATE DATABASE IF NOT EXISTS company_db;

Database creation is commonly done during project installation, deployment, testing, or when preparing a new environment for development.

MySQL Drop DB

The DROP DATABASE statement deletes an entire database, including all tables and data inside it.

DROP DATABASE company_db;

This command is dangerous because it permanently removes the database structure and the stored data. Before using it, you should always make sure that you are working on the correct database and that a backup exists if the data is important.

A safer version uses IF EXISTS to avoid an error if the database does not exist.

DROP DATABASE IF EXISTS company_db;

In production systems, dropping a database should be restricted to trusted administrators. Developers should never run destructive commands on a live database without review, backup, and confirmation.

MySQL Create Table

A table stores data in rows and columns. Each table should represent one clear concept, such as users, products, orders, posts, payments, comments, or categories.

The CREATE TABLE statement is used to define a table and its columns.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

This example creates a users table with four columns: id, name, email, and created_at.

The id column is an auto-incrementing primary key. The name column cannot be empty. The email column must be unique. The created_at column stores the date and time when the row was created.

Good table design is important because it affects performance, data quality, and maintainability. A well-designed table uses suitable data types, clear column names, meaningful constraints, and indexes where needed.

MySQL Drop Table

The DROP TABLE statement deletes a table and all data stored inside it.

DROP TABLE users;

This command permanently removes the table structure and records. It should be used carefully, especially in production environments.

You can also drop a table only if it exists.

DROP TABLE IF EXISTS users;

Dropping a table is different from deleting rows. DELETE removes data from a table, while DROP TABLE removes the table itself.

DELETE FROM users;

The DELETE statement keeps the table structure. DROP TABLE removes both the structure and the data.

MySQL Alter Table

The ALTER TABLE statement is used to change an existing table. It can add columns, modify columns, rename columns, drop columns, add constraints, or create indexes.

For example, you can add a phone column to the users table.

ALTER TABLE users
ADD phone VARCHAR(30);

You can modify the data type or size of an existing column.

ALTER TABLE users
MODIFY name VARCHAR(150) NOT NULL;

You can also drop a column if it is no longer needed.

ALTER TABLE users
DROP COLUMN phone;

In real projects, changing tables should be done carefully. If the table already contains data, altering columns may cause data loss, conversion problems, or application errors.

Frameworks such as Laravel often manage table changes through migrations. A migration is a version-controlled way to create and modify database tables safely across different environments.

MySQL Constraints

Constraints are rules applied to table columns to protect data integrity. They help ensure that the database contains valid, consistent, and reliable data.

Common MySQL constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.

Constraints are important because application code alone is not enough to protect data. A user, script, import tool, or another service may insert data into the database. Constraints make the database itself responsible for enforcing important rules.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    sku VARCHAR(100) UNIQUE,
    price DECIMAL(10, 2) CHECK (price >= 0),
    status VARCHAR(20) DEFAULT 'active'
);

This table uses multiple constraints to prevent invalid product data. The name is required, the SKU must be unique, the price cannot be negative, and the status has a default value.

MySQL NOT NULL

The NOT NULL constraint prevents a column from storing NULL values. It means the column must always have a value.

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL
);

In this example, every customer must have a name and an email. MySQL will reject any row that tries to insert NULL into these columns.

NOT NULL is useful for required fields such as names, emails, order numbers, product titles, user passwords, and foreign key references when the relationship is mandatory.

However, not every column should be NOT NULL. Optional data such as middle_name, second_phone, deleted_at, or notes can be nullable if the application allows empty values.

MySQL UNIQUE

The UNIQUE constraint prevents duplicate values in a column or group of columns.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE
);

This means two users cannot have the same email address.

UNIQUE constraints are commonly used for emails, usernames, product SKUs, invoice numbers, national IDs, slugs, and API tokens.

You can also define a unique rule across multiple columns. This is called a composite unique constraint.

CREATE TABLE course_registrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    UNIQUE (student_id, course_id)
);

In this example, the same student cannot be registered in the same course more than once.

MySQL Primary Key

A PRIMARY KEY uniquely identifies each row in a table. Every table should usually have a primary key.

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    body TEXT
);

The id column uniquely identifies each post. No two rows can have the same primary key value, and the primary key cannot be NULL.

Primary keys are used to find records, update records, delete records, create relationships, and connect tables using foreign keys.

Most MySQL tables use an auto-increment integer as the primary key. Some systems may use UUIDs instead, especially distributed systems, but auto-increment IDs are simple and common for many applications.

MySQL Foreign Key

A FOREIGN KEY creates a relationship between two tables. It ensures that a value in one table refers to a valid row in another table.

For example, an order usually belongs to a user. The orders table can store a user_id that references the users table.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

This means every order must belong to an existing user. MySQL will not allow an order to reference a user ID that does not exist.

Foreign keys protect relational integrity. They help avoid orphan records, such as orders without users, comments without posts, or order_items without orders.

Foreign keys can also define actions for updates and deletes.

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    body TEXT NOT NULL,
    FOREIGN KEY (post_id) REFERENCES posts(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

With ON DELETE CASCADE, deleting a post also deletes its related comments. This can be useful, but it must be used carefully because it can remove many related records automatically.

MySQL CHECK

The CHECK constraint is used to define a condition that values must satisfy before they can be inserted or updated.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2) CHECK (salary >= 0)
);

This prevents negative salaries from being stored.

CHECK constraints are useful for rules such as positive prices, valid percentages, minimum ages, accepted status values, and numeric ranges.

CREATE TABLE coupons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    discount_percent INT CHECK (discount_percent BETWEEN 1 AND 100)
);

This example allows discount percentages only between 1 and 100.

CHECK constraints should be used for simple data rules. More complex business logic is usually handled in application code or stored procedures.

MySQL DEFAULT

The DEFAULT constraint assigns a default value to a column when no value is provided during insertion.

CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    status VARCHAR(30) DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

If a task is inserted without a status, MySQL automatically stores pending. If created_at is not provided, MySQL stores the current date and time.

INSERT INTO tasks (title)
VALUES ('Prepare database backup');

DEFAULT values make inserts simpler and help maintain consistency. They are commonly used for statuses, timestamps, counters, flags, and configuration-like values.

MySQL Create Index

An index is a database structure that helps MySQL find data faster. It works like an organized lookup system for selected columns.

The CREATE INDEX statement creates an index on one or more columns.

CREATE INDEX idx_users_email
ON users (email);

This index can make searches by email faster.

SELECT *
FROM users
WHERE email = 'adnan@example.com';

Indexes are useful for columns used frequently in WHERE, JOIN, ORDER BY, and GROUP BY clauses.

CREATE INDEX idx_orders_user_id
ON orders (user_id);

This index can improve queries that retrieve orders for a specific user.

However, indexes are not free. They take storage space and can slow down INSERT, UPDATE, and DELETE operations because MySQL must update the index whenever the data changes.

A good indexing strategy focuses on real query patterns. Do not create indexes randomly. Create them for columns that are frequently searched, joined, filtered, or sorted.

MySQL Auto Increment

AUTO_INCREMENT automatically generates a new numeric value for a column whenever a new row is inserted. It is commonly used with primary keys.

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

When inserting a new category, you do not need to provide the id.

INSERT INTO categories (name)
VALUES ('Programming');

MySQL will automatically assign the next available number to the id column.

AUTO_INCREMENT is simple and practical for many systems. It makes records easy to reference and keeps primary key creation automatic.

One important detail is that deleted IDs are usually not reused. If row 5 is deleted, the next inserted row may still continue with a higher number. This is normal and should not be treated as an error.

MySQL Dates

MySQL provides several date and time data types. The most common ones are DATE, TIME, DATETIME, and TIMESTAMP.

• DATE: stores only a date, such as 2026-06-10.

• TIME: stores only a time, such as 14:30:00.

• DATETIME: stores date and time together.

• TIMESTAMP: stores date and time and is often used for created_at and updated_at values.

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    event_date DATE NOT NULL,
    starts_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Dates are important for filtering records, building reports, tracking activity, scheduling events, and managing logs.

SELECT *
FROM events
WHERE event_date >= '2026-06-01';

You can also use MySQL date functions such as NOW(), CURDATE(), DATE(), YEAR(), MONTH(), and DATEDIFF().

SELECT NOW();
SELECT CURDATE();
SELECT YEAR(created_at) FROM users;

In professional applications, time zones should be handled carefully. Many systems store dates in UTC in the database and convert them to the user's local time in the application.

MySQL Views

A view is a virtual table based on a SELECT query. It does not usually store data itself. Instead, it shows data from one or more tables through a saved query.

Views are created using the CREATE VIEW statement.

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

After creating the view, you can query it like a normal table.

SELECT *
FROM active_users;

Views are useful when you want to simplify complex queries, hide sensitive columns, provide cleaner reporting structures, or expose limited data to specific users or services.

For example, instead of repeating a long join query in many places, you can create a view and query it when needed.

CREATE VIEW order_summary AS
SELECT
    orders.id AS order_id,
    users.name AS customer_name,
    orders.total,
    orders.created_at
FROM orders
INNER JOIN users ON users.id = orders.user_id;

Views can improve readability, but they should not be used as a replacement for proper table design. Very complex views can become hard to debug and may affect performance if they are used carelessly.

MySQL Injection

SQL injection is a serious security vulnerability that happens when user input is inserted directly into an SQL query without proper protection.

For example, this kind of query is dangerous when user input is concatenated directly into the SQL string.

SELECT *
FROM users
WHERE email = '$email' AND password = '$password';

If an attacker controls the input, they may try to change the meaning of the query. This can lead to unauthorized login, data leaks, data modification, or even deletion of database records.

SQL injection is not only a MySQL problem. It can happen in many database systems when developers build SQL queries incorrectly.

The main causes of SQL injection are:

• Concatenating user input directly into SQL queries.

• Trusting form data, URL parameters, cookies, or headers without validation.

• Using raw SQL without parameter binding.

• Displaying detailed database errors to users.

• Giving the database user more permissions than necessary.

To reduce SQL injection risks, developers should use prepared statements, validate inputs, escape output where needed, use least-privilege database accounts, and avoid exposing internal error details.

MySQL Prepared Statements

Prepared statements are one of the most important ways to protect SQL queries from injection attacks. They separate the SQL structure from the user-provided values.

Instead of placing user input directly into the query, placeholders are used. The values are then bound safely.

SELECT *
FROM users
WHERE email = ?;

In PHP, prepared statements are commonly used with PDO.

<?php
$pdo = new PDO(
    'mysql:host=localhost;dbname=company_db;charset=utf8mb4',
    'root',
    '',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

$email = $_POST['email'] ?? '';

$stmt = $pdo->prepare('SELECT id, name, email FROM users WHERE email = :email');
$stmt->execute([
    'email' => $email,
]);

$user = $stmt->fetch(PDO::FETCH_ASSOC);
?>

In this example, :email is a named placeholder. The actual email value is passed separately to execute(). PDO handles the value safely instead of treating it as part of the SQL command.

Prepared statements should be used for SELECT, INSERT, UPDATE, and DELETE queries whenever user input is involved.

<?php
$stmt = $pdo->prepare(
    'INSERT INTO users (name, email) VALUES (:name, :email)'
);

$stmt->execute([
    'name' => $name,
    'email' => $email,
]);
?>

Prepared statements do not replace all security practices, but they are a core requirement for secure database programming.

How These MySQL Concepts Work Together

In a real application, these concepts are not isolated. They work together to create a reliable and secure database layer.

For example, an e-commerce system may create a database, define tables for users and orders, use primary keys and foreign keys to connect records, add indexes for faster searching, use default timestamps for tracking, create views for reports, and use prepared statements to protect user input.

CREATE DATABASE IF NOT EXISTS shop_db;
USE shop_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL CHECK (total >= 0),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_orders_user_id
ON orders (user_id);

CREATE VIEW user_order_summary AS
SELECT
    users.id AS user_id,
    users.name,
    users.email,
    COUNT(orders.id) AS total_orders,
    SUM(orders.total) AS total_spent
FROM users
LEFT JOIN orders ON orders.user_id = users.id
GROUP BY users.id, users.name, users.email;

This example shows database creation, table creation, constraints, auto increment, primary keys, foreign keys, indexes, dates, and views in one structure.

The application should then use prepared statements when inserting, updating, deleting, or retrieving data based on user input.

Best Practices for MySQL Database Design

Good MySQL design is about more than writing valid SQL. It requires clear structure, data integrity, performance awareness, and security.

• Use clear and consistent database, table, and column names.

• Choose suitable data types for each column.

• Add primary keys to important tables.

• Use foreign keys when relationships must be protected.

• Use NOT NULL for required fields.

• Use UNIQUE for values that must not be duplicated.

• Use DEFAULT values for common automatic values.

• Create indexes based on real query needs.

• Be careful with DROP DATABASE and DROP TABLE commands.

• Use prepared statements for all user input.

• Keep backups before destructive changes.

• Use migrations to track database changes in professional projects.

Conclusion

MySQL database structure is a core skill for backend developers, full-stack developers, and anyone working with data-driven applications. Understanding how to create databases, design tables, define constraints, use indexes, handle dates, create views, and protect queries from SQL injection helps developers build safer and more reliable systems.

Commands such as CREATE DATABASE, CREATE TABLE, ALTER TABLE, and CREATE INDEX define the structure of the database. Constraints such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT protect the quality of stored data. Views simplify data access, while prepared statements protect applications from dangerous SQL injection attacks.

Learning these topics carefully gives you a strong foundation for working with MySQL in real projects, whether you are building a simple blog, a Laravel application, an admin dashboard, an e-commerce platform, or a large backend system.