Can you explain the concept of foreign key constraints with cascading actions like CASCADE, SET NULL, and SET DEFAULT in MySQL? — MySQL Interview Question #45

In MySQL, a foreign key constraint is a key that refers to the primary key of another table. This constraint ensures that the data being inserted or updated in a table is consistent with the related table. Foreign key constraints can also be used to automatically perform cascading actions when a primary key is updated or deleted.

There are three types of cascading actions in MySQL:

1. CASCADE: When a record is updated or deleted from the primary key table, the changes are cascaded to the foreign key table. For example, if a record in the primary key table is deleted, all related records in the foreign key table are also deleted.

2. SET NULL: When a record is updated or deleted from the primary key table, the corresponding value in the foreign key table is set to NULL. For example, if a record in the primary key table is deleted, the corresponding foreign key value in the related table is set to NULL.

3. SET DEFAULT: When a record is updated or deleted from the primary key table, the corresponding value in the foreign key table is set to its default value. The default value must be defined when the foreign key constraint is created.

Here’s an example of how to create a foreign key constraint with a cascading action in MySQL:

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

CREATE TABLE customers (
  customer_id INT,
  customer_name VARCHAR(20),
  PRIMARY KEY (customer_id),
  FOREIGN KEY (customer_id)
    REFERENCES orders(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

In this example, the ‘customers‘ table has a foreign key constraint that refers to the ‘customer_id‘ column of the ‘orders‘ table. The ‘CASCADE‘ action is specified for both the ‘ON DELETE‘ and ‘ON UPDATE‘ options, which means that any changes made to the ‘customer_id‘ column of the ‘orders‘ table will be cascaded to the ‘customer_id‘ column of the ‘customers‘ table.