SQL & MySQL Interview Questions 6-10 (Constraints, Schema, Indexes)

Hello! In the first lesson, we learned what a database is and covered the core concepts of tables, primary keys, and foreign keys.

In this lesson, we'll build on that foundation. We'll talk about the "rules" that protect your data (constraints), the "blueprint" that organizes your database (schema), and the "magic" that makes it all fast (indexes). These topics are essential for understanding how to build a database that is not just functional, but also reliable and efficient.

6. What are MySQL constraints? List common types.

Constraints are rules you apply to the columns of a table to enforce data integrity and accuracy.

Analogy: Think of constraints as the "bouncers" or "house rules" for your table. They stand at the door (your `INSERT` or `UPDATE` query) and check new data. If the data violates a rule, it's rejected and not allowed into the table. This is critical for preventing "bad data" from ever getting into your system.

Here are the most common types:

Here's an SQL example showing several constraints in action:

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    
    name VARCHAR(100) NOT NULL,
    
    sku VARCHAR(50) UNIQUE NOT NULL,
    
    -- A CHECK constraint to enforce a business rule
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    
    -- A DEFAULT constraint
    status VARCHAR(20) DEFAULT 'pending'
);

-- This INSERT will work
INSERT INTO Products (product_id, name, sku, price) 
VALUES (1, 'Blue Widget', 'BW-001', 9.99);

-- This INSERT will FAIL due to the CHECK constraint
-- INSERT INTO Products (product_id, name, sku, price) 
-- VALUES (2, 'Red Widget', 'RW-002', -5.00);

Sample Output (for the first INSERT):

Query OK, 1 row affected

Sample Output (for the second, failed INSERT):

ERROR: CHECK constraint "products_price_check" is violated

7. What is a database schema?

A database schemais the logical blueprint for the entire database. It defines all the tables, the columns in those tables, the data type for each column, and, most importantly, the relationships(foreign keys) between the tables.

Analogy: If the database is a house, the schema is the architect's complete set of blueprints .

The blueprint doesn't just show one room (a table). It shows all the rooms, what they are called (`Users`, `Posts`), what's in them (columns like `username`, `title`), and how they are connected (the hallway or `FOREIGN KEY` that links a `Post` to a `User`).

When someone asks for your database schema, they are asking for the high-level design of your database structure. In MySQL, you can create a new schema (which is synonymous with a "database") like this:

-- This creates a new, empty database (a "schema")
CREATE SCHEMA IF NOT EXISTS my_ecommerce_app;

-- This tells MySQL to use this database for all
-- subsequent commands (like CREATE TABLE)
USE my_ecommerce_app;

Sample Output:

Query OK, 1 row affected
Database changed

8. What are indexes in MySQL? Why are they used?

An index is a special data structure that the database uses to find rows dramatically faster .

Analogy: An index in a database is exactly like the index at the back of a textbook .

Indexes are used on columns that are frequently searched in a `WHERE` clause (like `username` or `email`). They are the single most important tool you have to speed up `SELECT` queries.

Trade-off: Indexes speed up reads (`SELECT`) but slow down writes (`INSERT`, `UPDATE`, `DELETE`). Why? Because when you add a new chapter to the textbook, you also have to go and update the index at the back.

-- We create an index on the 'email' column of our
-- 'Users' table, because we search for users
-- by their email all the time.
CREATE INDEX idx_users_email ON Users(email);

Sample Output:

Query OK, 0 rows affected

9. When should indexes be avoided?

This is a great follow-up question. Since indexes have a write-cost, you don't want to index every column.

Here is when you should avoidcreating an index:

10. What is the difference between clustered and non-clustered indexes?

This is a more advanced index question that separates candidates who have a deeper understanding.

Analogy:We'll use two types of books.

Key Takeaway for MySQL:

- When you declare a `PRIMARY KEY` on an InnoDB table, you are creating a Clustered Index . The database will physically sort the table data based on this key.

- When you `CREATE INDEX` on another column, you are creating a Non-Clustered Index (also called a secondary index).