MySQL Interview Questions and Answers

Question: Explain MySQL ACID properties.

Answer:

In the context of MySQL, ACID stands for Atomicity, Consistency, Isolation, and Durability. These are a set of properties that guarantee that database transactions are processed reliably and ensure data integrity even in the event of failures (like power outages, system crashes, or software bugs). Let’s go over each ACID property in detail:

1. Atomicity:

• Definition: Atomicity ensures that each transaction is treated as a single unit, which either completes entirely or does not happen at all. In other words, a transaction will be fully committed or fully rolled back—there is no in-between.
• Example: If a transaction involves transferring money between two accounts, either both the debit from one account and the credit to the other account will happen, or neither will happen. If something goes wrong during the process, the database ensures that the transaction is rolled back completely to avoid partial updates (e.g., a debit without the corresponding credit).
• Why it’s important:
  • Prevents partial or incomplete transactions that could lead to data inconsistencies.
  • Ensures that if any part of a transaction fails, the system can undo all changes made by that transaction.

2. Consistency:

• Definition: Consistency ensures that a transaction takes the database from one valid state to another valid state. The database must follow all the predefined rules (like constraints, triggers, and cascades), ensuring the integrity of the data is maintained.
• Example: If you have a rule that an account balance cannot go below zero, a transaction that tries to withdraw money from an account with insufficient funds will violate this rule and will be rolled back, ensuring that the database stays in a consistent state.
• Why it’s important:
  • It guarantees that only valid data is stored in the database.
  • Ensures that any transaction maintains the business rules and data integrity, such as constraints (e.g., primary keys, foreign keys) or checks (e.g., balance > 0).

3. Isolation:

• Definition: Isolation ensures that the operations of one transaction are isolated from those of other concurrent transactions. Even if multiple transactions are being executed simultaneously, each transaction should be unaware of others and behave as if it is the only transaction running at that time.
• Example: If two users are updating their account balances simultaneously, Isolation ensures that one transaction doesn’t interfere with the other. Each transaction will either see the data before any changes are made or after all changes are complete, never in between.
• Why it’s important:
  • Prevents situations where transactions interfere with each other, leading to issues like dirty reads, non-repeatable reads, or phantom reads.
  • Ensures that transactions execute independently, providing the correct results even in a concurrent processing environment.

Isolation Levels: MySQL provides several isolation levels that control the extent to which transactions are isolated from each other:

• Read Uncommitted: Transactions can read uncommitted changes from other transactions (can lead to dirty reads).
• Read Committed: Transactions can only read committed changes from other transactions (prevents dirty reads, but non-repeatable reads are possible).
• Repeatable Read: Ensures that once a transaction reads a value, subsequent reads will see the same value even if other transactions modify the data (prevents dirty reads and non-repeatable reads).
• Serializable: The highest level of isolation, which forces transactions to be executed serially (one at a time), preventing all anomalies but also reducing concurrency.

4. Durability:

• Definition: Durability ensures that once a transaction has been committed, it will persist in the database, even in the event of a system crash, power failure, or other unforeseen issues. The changes made by a committed transaction are permanent.
• Example: After a transaction commits a change (like transferring funds), that change is written to disk and will not be lost, even if the server crashes immediately after the commit.
• Why it’s important:
  • Guarantees that committed transactions are stored safely and reliably in the database, ensuring no data loss after a successful transaction.

Summary of ACID Properties:

Property	Description	Example
Atomicity	Ensures that a transaction is fully completed or not executed at all.	A bank transaction where money is both debited from one account and credited to another. If one part fails, the entire transaction is rolled back.
Consistency	Ensures the database transitions from one valid state to another, maintaining data integrity and business rules.	A bank account transaction fails if it tries to make a withdrawal that would result in a negative balance.
Isolation	Ensures that transactions are isolated from each other, preventing interference.	Two users transferring money from their accounts simultaneously won’t interfere with each other’s transactions.
Durability	Ensures that once a transaction is committed, it will persist even in case of a system failure.	A successful transfer of funds is saved to disk, and no data is lost even if the server crashes right after committing.

Why ACID Properties Matter in MySQL:

• Reliability: ACID properties ensure that MySQL provides a reliable and consistent environment for transaction processing, which is especially important for applications like banking, e-commerce, and any system that deals with sensitive and critical data.
• Error Prevention: They prevent errors caused by incomplete, inconsistent, or corrupted transactions, maintaining the integrity of the database.
• Data Integrity: They ensure that business rules and data validation processes are respected during every transaction.

Example of Transaction in MySQL:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- If everything goes fine, commit the transaction
COMMIT;

-- If something goes wrong, roll back the transaction
ROLLBACK;

In this example, if the second UPDATE fails (e.g., due to insufficient funds or any other error), the first UPDATE is also rolled back, ensuring that no partial changes are made. The database maintains its integrity, and the transaction is either fully completed or fully rolled back.

Question: What are foreign keys in MySQL?

Answer:

A foreign key in MySQL is a constraint used to establish and enforce a link between the data in two tables. It ensures that the value in a column (or a set of columns) in one table matches a valid value in the primary key or unique key column(s) of another table. This is used to maintain referential integrity between the two tables, ensuring that relationships between the data are consistent.

Key Concepts:

1. Referential Integrity:

   • A foreign key ensures that values in one table (the child table) correspond to values in another table (the parent table). This helps to maintain consistency and validity in the database.

2. Parent Table:

   • The table containing the primary key (or unique key) that the foreign key refers to. This table is often called the “parent” table.

3. Child Table:

   • The table containing the foreign key, which references the primary key in the parent table. This table is often called the “child” table.

How Foreign Keys Work:

• A foreign key column in a child table must match a value in the primary key or unique key column of the parent table.
• If an invalid value is inserted into the foreign key column (i.e., a value that does not exist in the parent table), MySQL will raise an error.
• Foreign keys help to ensure that there are no “orphan” records in the child table (i.e., records that refer to non-existent entries in the parent table).

Syntax to Create a Foreign Key:

CREATE TABLE child_table (
  id INT PRIMARY KEY,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);

• parent_id in the child_table is the foreign key.
• id in the parent_table is the primary key that the foreign key references.

Example of Foreign Key:

Let’s say we have two tables: orders and customers. Each order is placed by a customer, so the orders table has a foreign key that references the customers table.

1. customers table (Parent Table):

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

2. orders table (Child Table):

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

Here:

• customer_id in the orders table is the foreign key that references the customer_id in the customers table.
• The foreign key ensures that every order in the orders table is associated with a valid customer in the customers table.

Foreign Key Constraints:

When defining a foreign key, you can specify how to handle actions like updates or deletions in the parent table. These actions are defined using the ON DELETE and ON UPDATE clauses. The possible actions are:

1. ON DELETE CASCADE:

   • If a record in the parent table is deleted, all corresponding records in the child table are automatically deleted.
   • Example: If a customer is deleted, all their orders are also deleted.

   FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE;

2. ON DELETE SET NULL:

   • If a record in the parent table is deleted, the corresponding foreign key value in the child table is set to NULL.
   • Example: If a customer is deleted, the customer_id in the orders table is set to NULL, but the order is retained.

   FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL;

3. ON DELETE RESTRICT:

   • Prevents deletion of a record in the parent table if there are any corresponding records in the child table. This ensures that you cannot delete a customer if they have any orders.

   FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT;

4. ON DELETE NO ACTION:

   • Similar to RESTRICT, it prevents deletion of a parent record if related child records exist. However, NO ACTION is handled by the database in a slightly different way and is essentially the same as RESTRICT in MySQL.

   FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE NO ACTION;

5. ON UPDATE CASCADE:

   • If the primary key value in the parent table is updated, all corresponding foreign key values in the child table are automatically updated to match.
   • Example: If a customer’s customer_id is updated, the customer_id in the orders table will be updated accordingly.

   FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON UPDATE CASCADE;

Example with Foreign Key Constraints:

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

In this example:

• If a customer_id in the customers table is deleted, all corresponding records in the orders table will be deleted (ON DELETE CASCADE).
• If a customer_id in the customers table is updated, all corresponding customer_id values in the orders table will be updated (ON UPDATE CASCADE).

Advantages of Using Foreign Keys:

1. Referential Integrity:

   • Ensures that relationships between tables remain consistent, preventing invalid or orphaned records.

2. Data Integrity:

   • Prevents actions that could lead to invalid data, such as inserting a record in the child table without a corresponding record in the parent table.

3. Data Normalization:

   • Helps normalize the database by splitting data into related tables, ensuring data redundancy is minimized.

4. Automatic Handling of Related Data:

   • With the appropriate constraints like ON DELETE CASCADE or ON UPDATE CASCADE, foreign keys can automatically handle related data when the parent table’s data changes.

Summary:

• Foreign Key: A constraint that establishes a relationship between two tables, ensuring data consistency and referential integrity.
• Parent Table: The table that contains the primary or unique key.
• Child Table: The table that contains the foreign key pointing to the parent table.
• Referential Integrity: Ensures that foreign keys only refer to valid records in the parent table.
• Actions: Foreign keys allow you to define actions like CASCADE, SET NULL, RESTRICT, and NO ACTION for managing updates or deletions.

Foreign keys are an essential feature for maintaining relationships between tables and ensuring data consistency in relational databases like MySQL.

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.