Questions

14 of 26

1What is a key in MySQL and why is it used?

2What is the difference between a key and an index?

3What is a primary key? Can a table have more than one primary key?

4What is a foreign key and why is it important in relational databases?

5How do you define a primary key when creating a table?

6Can a primary key column contain NULL values? Why or why not?

7What is the difference between a UNIQUE key and a PRIMARY key?

8What is an AUTO_INCREMENT key, and how does it work with the primary key?

9Can two tables have the same primary key name? Explain.

10What is the purpose of the FOREIGN KEY constraint in maintaining referential integrity?

11What is a composite key? How do you define one in MySQL?

12What are candidate keys, and how do they differ from alternate keys?

13What happens if you try to insert a duplicate value into a UNIQUE key column?

14How do foreign key constraints behave on DELETE and UPDATE operations (CASCADE, SET NULL, etc.)?

15What are the differences between ON DELETE CASCADE and ON DELETE SET NULL? What is a super key, and how does it relate to candidate keys? Can a foreign key reference a non-primary key column in another table?

16How do you disable and re-enable foreign key checks in MySQL? Why would you do that?

17How do you drop a primary key or foreign key from an existing table?

18How can you find all foreign key relationships in a MySQL database?

19How does InnoDB enforce foreign key constraints internally?

20What is the impact of defining multiple UNIQUE keys on the same table?

21Can you define a foreign key constraint referencing a table in a different database schema?

22Explain how key definitions affect indexing and query optimization.

23What are the differences between logical keys (like candidate or composite keys) and physical indexes?

24Can a composite key contain a foreign key as one of its columns? Provide an example.

25How does MySQL handle updates to parent keys that are referenced by foreign keys?

26What are deferred constraint checks, and does MySQL support them? How do you design keys in a many-to-many relationship using a junction (bridge) table? What are the potential performance implications of using multiple foreign keys and composite keys in large-scale systems?

mysql

Functions and Operators

14 / 26

How do foreign key constraints behave on DELETE and UPDATE operations (CASCADE, SET NULL, etc.)?

Behavior of FOREIGN KEY Constraints on DELETE and UPDATE

Foreign key constraints define how MySQL should react when a referenced (parent table) row is deleted or updated. These actions ensure referential integrity is maintained.

1. ON DELETE / ON UPDATE Actions

1
CASCADE → Automatically deletes or updates the child rows when the parent row changes.
2
SET NULL → Sets the foreign key value in the child row to NULL.
3
RESTRICT → Prevents the operation if child rows exist (default behavior in many cases).
4
NO ACTION → Same as RESTRICT in MySQL; the delete/update is blocked.
5
SET DEFAULT → Sets child rows to a default value (not supported by MySQL).

Example: Defining Foreign Key Actions

2. Behavioral Details

1
ON DELETE CASCADE → Deleting a customer automatically deletes their orders.
2
ON UPDATE CASCADE → Updating the customer's ID updates matching order.customer_id values.
3
ON DELETE SET NULL → Deletes parent row but child.foreign_key becomes NULL.
4
RESTRICT / NO ACTION → Operation is blocked if dependent rows exist.

Example: SET NULL Behavior

These actions give fine-grained control over how relational data behaves when parent records change, ensuring data integrity according to the database design.