MySQL Interview Questions and Answers(2025)

Question: What is the difference between DELETE, TRUNCATE, and DROP in MySQL?

Answer:

In MySQL, DELETE, TRUNCATE, and DROP are all commands used for removing data, but they differ in their functionality, scope, and performance. Let’s break down the differences between them.

1. DELETE:

• Purpose: The DELETE statement is used to remove rows from a table based on a specific condition.

• Syntax:

  DELETE FROM table_name WHERE condition;

• Key Features:

  • Row-level operation: DELETE removes rows one by one, based on the condition specified in the WHERE clause.
  • Transaction-safe: It is a transactional operation. This means if you are using a transactional storage engine like InnoDB, you can roll back the changes if a transaction fails.
  • Can be selective: You can delete specific rows by specifying a condition in the WHERE clause (e.g., delete rows where a certain column matches a value).
  • Triggers: DELETE can activate triggers if they are defined on the table.
  • Performance: DELETE can be slow for large tables because it removes rows one at a time and also maintains transaction logs.
  • Space Reclamation: After deleting rows, space is not immediately reclaimed. The table structure remains unchanged unless you run OPTIMIZE TABLE to defragment the table.
  • Effect on indexes: The DELETE operation does not affect the structure of the table but updates the indexes for the removed rows.

• Example:

  DELETE FROM employees WHERE employee_id = 101;

2. TRUNCATE:

• Purpose: The TRUNCATE statement is used to remove all rows from a table without logging individual row deletions.

• Syntax:

  TRUNCATE TABLE table_name;

• Key Features:

  • Table-level operation: TRUNCATE removes all rows in a table, but the structure of the table (its schema, column definitions, indexes) remains intact.
  • Faster than DELETE: TRUNCATE is typically faster than DELETE because it does not log each individual row deletion. It essentially deallocates the data pages used by the table, which is more efficient.
  • Non-transactional: It is not transaction-safe in some cases, especially in MySQL’s default storage engine (InnoDB). Once you execute TRUNCATE, it cannot be rolled back unless you’re using InnoDB with explicit transaction handling.
  • Cannot delete specific rows: You cannot use a WHERE clause with TRUNCATE. It always removes all rows from the table.
  • Resets auto-increment: If the table has an AUTO_INCREMENT column, TRUNCATE resets the counter to the starting value (typically 1).
  • Does not activate triggers: Unlike DELETE, TRUNCATE does not activate any DELETE triggers.
  • Does not reclaim space immediately**: Space used by the data is deallocated, but in some cases, the disk space may not be fully freed up until a new row is inserted into the table.

• Example:

  TRUNCATE TABLE employees;

3. DROP:

• Purpose: The DROP statement is used to remove an entire table or database, including all its structure, data, and associated constraints.

• Syntax:

  DROP TABLE table_name;

  Or to drop a database:

  DROP DATABASE database_name;

• Key Features:

  • Table and Database-level operation: DROP completely removes the table or database from the database system.
  • Irreversible: Once a DROP command is executed, the table or database and all of its data cannot be recovered (unless there are backups).
  • No transaction rollback: DROP cannot be rolled back, even in a transactional engine like InnoDB. It is a non-transactional operation.
  • Deletes the structure: Unlike DELETE and TRUNCATE, which only remove data, DROP removes the table’s structure entirely, along with its data.
  • No auto-increment reset: Since the table is removed, there’s no concept of resetting the auto-increment value—it is entirely gone.
  • Removes indexes, constraints, triggers, and relationships: Any foreign key constraints, indexes, and triggers associated with the table are also removed when the table is dropped.

• Example:

  DROP TABLE employees;

---

Summary of Differences:

Feature	DELETE	TRUNCATE	DROP
Operation Level	Row-level operation	Table-level operation	Table or database-level operation
Data Removal	Selective (can use WHERE clause)	All data in the table	Entire table or database
Performance	Slower (logs each row)	Faster (does not log individual rows)	Very fast (removes entire table structure)
Transactional	Yes (can be rolled back in transactions)	No (cannot be rolled back in most cases)	No (cannot be rolled back)
Space Reclamation	No immediate reclamation	Frees up space immediately	Frees up space immediately
Triggers	Activates triggers	Does not activate triggers	No triggers involved
Auto-Increment Reset	No	Yes	N/A
Impact on Table Structure	Table structure remains intact	Table structure remains intact	Table structure and all associated objects are removed
Use Case	Deleting specific rows	Removing all rows from a table	Completely removing a table or database

---

When to Use Each:

• DELETE:

  • Use when you want to remove specific rows based on a condition.
  • Suitable when you need to maintain a transaction-safe environment.
  • Ideal when you need to trigger DELETE triggers or when the operation is selective.

• TRUNCATE:

  • Use when you want to quickly remove all rows from a table, but keep the table structure intact for future use.
  • Suitable for clearing a table before refilling it with new data.
  • Faster than DELETE for large datasets when you don’t need to log individual row deletions or activate triggers.

• DROP:

  • Use when you want to completely remove a table or database, along with all its data and structure.
  • Ideal when you no longer need the table or database, and you want to free up all associated resources.

Conclusion:

Each of these commands has its own use cases based on the level of operation (rows, tables, or entire databases) and the need for transaction safety, performance, and resource reclamation. DELETE is more flexible but slower, TRUNCATE is faster but less flexible, and DROP is the most drastic, completely removing the table and its data.

Question: What is a primary key in MySQL?

Answer:

A primary key in MySQL is a constraint that uniquely identifies each record in a database table. It ensures that no two rows in the table can have the same value for the primary key column(s). The primary key is used to enforce the uniqueness of data and create a relationship between tables.

Key Features of a Primary Key:

1. Uniqueness:

   • The value in a primary key column must be unique for each row. This ensures that each record can be uniquely identified in the table.

2. Not NULL:

   • A primary key column cannot contain NULL values. Each row must have a valid value in the primary key column to ensure it can be uniquely identified.

3. Single or Composite:

   • A primary key can consist of one column (single column primary key) or multiple columns (composite primary key). In the case of a composite primary key, the combination of values in the multiple columns must be unique.

4. Indexing:

   • A primary key automatically creates a unique index on the column(s), which improves the performance of queries that search for data based on the primary key.

5. One Primary Key per Table:

   • A table can only have one primary key, but the primary key can consist of more than one column (composite primary key).

6. Referential Integrity:

   • The primary key is often used in relationships between tables, such as when another table includes a foreign key that references the primary key to establish a relationship between the tables.

Syntax to Create a Primary Key:

1. Single Column Primary Key:

   CREATE TABLE employees (
     employee_id INT NOT NULL,
     name VARCHAR(100),
     PRIMARY KEY (employee_id)
   );

   In this example, employee_id is the primary key. It is unique and cannot be NULL.

2. Composite Primary Key:

   CREATE TABLE orders (
     order_id INT NOT NULL,
     customer_id INT NOT NULL,
     order_date DATE,
     PRIMARY KEY (order_id, customer_id)
   );

   In this example, the combination of order_id and customer_id forms a composite primary key. The combination of values in these two columns must be unique.

How Primary Key Works:

• A primary key uniquely identifies each row in the table. For example, if you’re looking for a specific employee in the employees table, the database uses the primary key (employee_id) to quickly find the record.
• If you attempt to insert a row with a duplicate value in the primary key column, MySQL will reject the insertion with an error because it violates the uniqueness constraint.

Example of Primary Key Enforcement:

CREATE TABLE users (
  user_id INT AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  PRIMARY KEY (user_id)
);

• In this case, user_id is the primary key. It is automatically indexed, and no two users can have the same user_id.

• The AUTO_INCREMENT attribute ensures that user_id is automatically generated with a unique value for each row inserted into the table.

Primary Key Constraints:

• Uniqueness: Each value in the primary key column(s) must be unique across the table.
• Non-nullability: Primary key columns cannot contain NULL values.
• Indexing: MySQL automatically creates an index on the primary key column(s), which speeds up lookup queries based on the primary key.

Relationship with Foreign Keys:

• A primary key is often used to reference a table in other tables. For example, a foreign key in another table can refer to the primary key of a parent table, establishing a relationship between the two tables.

Example of Foreign Key Relationship:

Consider two tables: orders and customers.

1. customers table (Parent table with primary key):

   CREATE TABLE customers (
     customer_id INT AUTO_INCREMENT,
     name VARCHAR(100),
     PRIMARY KEY (customer_id)
   );

2. orders table (Child table with foreign key):

   CREATE TABLE orders (
     order_id INT AUTO_INCREMENT,
     order_date DATE,
     customer_id INT,
     PRIMARY KEY (order_id),
     FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
   );

In this example:

• customer_id in the customers table is the primary key.
• customer_id in the orders table is a foreign key that references the primary key in the customers table to establish a relationship between the two tables.

Benefits of Using Primary Keys:

1. Data Integrity: By enforcing uniqueness and preventing NULL values, primary keys help maintain the integrity of the data in the table.
2. Performance: The automatic indexing of the primary key improves the speed of queries that search for records by the primary key.
3. Relationships: Primary keys are used to establish relationships between tables, which is fundamental to database normalization and the creation of relational databases.

Summary:

• A primary key is a column or a combination of columns in a table that uniquely identifies each row.
• It must be unique and cannot contain NULL values.
• Each table can only have one primary key, but that key can consist of multiple columns (composite key).
• Primary keys automatically create an index, which improves query performance.
• Primary keys are commonly used to create relationships between tables in a relational database.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as MySQL interview questions, MySQL interview experiences, and details about various MySQL job positions. Click here to check it out.