Navigate by Range

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:

NOT NULL: Ensures a column cannot have an empty (`NULL`) value. You must provide a value for this field.
UNIQUE: Ensures that all values in a column (or set of columns) are unique. (We discussed this in the last lesson!)
PRIMARY KEY: A combination of `NOT NULL` and `UNIQUE`. This is the main identifier for the row.
FOREIGN KEY: Links this table to a `PRIMARY KEY` in another table, enforcing referential integrity.
CHECK: This is a custom rule. It ensures that a value meets a specific condition. For example, `(age >= 18)` or `(price > 0)`.
DEFAULT: This isn't a "bouncer" so much as a "helpful host." It provides a default value for a column if no value is specified in the `INSERT` statement.

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 .

Without an Index (Slow):You want to find the word "Python" in a 500-page book. You have to start at page 1 and read every single pageuntil you find it. This is a Full Table Scan .
With an Index (Fast):You flip to the index at the back. You find "Python," and it tells you, "see pages 42, 119, and 301." You can jump directlyto those pages.

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:

On Small Tables: If the "textbook" is just a 10-page pamphlet, it's faster for the database to just read the whole thing (full scan) than to bother looking at the index.
On Write-Heavy Tables: If you have a table that gets millions of `INSERTs` per minute (like a log table), but very few `SELECTs`, adding an index will cripple your write performance.
On Columns with Low Cardinality (Very Important): Cardinality means "uniqueness."
- High Cardinality (good for index): `email`, `username`. Almost every value is unique.
- Low Cardinality (bad for index): `gender`, `status` (e.g., 'active', 'inactive'), `is_paid` (true/false).
Analogy:An index on a `gender` column is like an index that says "Words starting with 'A': pages 1-250. Words starting with 'B': pages 251-500." It's useless for finding anything quickly. An index is only useful if it's highly selective.

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.

Clustered Index = A Phone Book
In a phone book, the data is physically stored in the order of the index (alphabetically by last name). The data is the index. You cannot have two phone books, one sorted by name and one sorted by city, with the same data.
Because the data is physically sorted, a table can have only one clustered index. In MySQL's InnoDB engine, the Primary Key is your clustered index.
Non-Clustered Index = A Textbook Index
This is the "index at the back of the book" we talked about. It is a separate structure that lives apart from the data. The textbook pages (the data) are just in order by page number (e.g., in the order they were inserted).
The index stores the keyword (e.g., `email`) and a pointer(e.g., "go to row 734") to find the actual data. You can have many non-clustered indexes on a table (an index for `email`, one for `username`, one for `zip_code`, etc.).

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).

Check your understanding

2 questions · no sign-up, nothing stored

0/2 answered

1.What is the main cost of adding an index?

Reads become slowerThe table can no longer be joinedWrites become slower and the index consumes storageTransactions stop being ACID

2.How many clustered indexes can a table have, and why?

As many as you likeExactly one - it determines the physical row order on diskOne per column typeNone - clustered indexes were removed in MySQL 8

0 / 74 ranges

Progress is stored in this browser only - no sign-up, nothing sent anywhere.

Watch these run

Step through the algorithms behind these answers one operation at a time, forwards or backwards, with the code highlighted as it runs.

Binary search tree

Database indexes are B-trees; this is the same idea, one node at a time.

Go deeper

The same topic at architecture level, in the system design curriculum.

Database indexes and query performance

Leftmost-prefix rules, covering indexes and an interactive index explorer.

Previous Next

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

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.

Here are the most common types:

NOT NULL: Ensures a column cannot have an empty (`NULL`) value. You must provide a value for this field.
UNIQUE: Ensures that all values in a column (or set of columns) are unique. (We discussed this in the last lesson!)
PRIMARY KEY: A combination of `NOT NULL` and `UNIQUE`. This is the main identifier for the row.
FOREIGN KEY: Links this table to a `PRIMARY KEY` in another table, enforcing referential integrity.
CHECK: This is a custom rule. It ensures that a value meets a specific condition. For example, `(age >= 18)` or `(price > 0)`.
DEFAULT: This isn't a "bouncer" so much as a "helpful host." It provides a default value for a column if no value is specified in the `INSERT` statement.

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?

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

-- 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 .

Without an Index (Slow):You want to find the word "Python" in a 500-page book. You have to start at page 1 and read every single pageuntil you find it. This is a Full Table Scan .
With an Index (Fast):You flip to the index at the back. You find "Python," and it tells you, "see pages 42, 119, and 301." You can jump directlyto those pages.

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.

-- 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:

On Small Tables: If the "textbook" is just a 10-page pamphlet, it's faster for the database to just read the whole thing (full scan) than to bother looking at the index.
On Write-Heavy Tables: If you have a table that gets millions of `INSERTs` per minute (like a log table), but very few `SELECTs`, adding an index will cripple your write performance.
On Columns with Low Cardinality (Very Important): Cardinality means "uniqueness."
- High Cardinality (good for index): `email`, `username`. Almost every value is unique.
- Low Cardinality (bad for index): `gender`, `status` (e.g., 'active', 'inactive'), `is_paid` (true/false).
Analogy:An index on a `gender` column is like an index that says "Words starting with 'A': pages 1-250. Words starting with 'B': pages 251-500." It's useless for finding anything quickly. An index is only useful if it's highly selective.

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.

Clustered Index = A Phone Book
In a phone book, the data is physically stored in the order of the index (alphabetically by last name). The data is the index. You cannot have two phone books, one sorted by name and one sorted by city, with the same data.
Because the data is physically sorted, a table can have only one clustered index. In MySQL's InnoDB engine, the Primary Key is your clustered index.
Non-Clustered Index = A Textbook Index
This is the "index at the back of the book" we talked about. It is a separate structure that lives apart from the data. The textbook pages (the data) are just in order by page number (e.g., in the order they were inserted).
The index stores the keyword (e.g., `email`) and a pointer(e.g., "go to row 734") to find the actual data. You can have many non-clustered indexes on a table (an index for `email`, one for `username`, one for `zip_code`, etc.).