Search code examples
sqlmysqlconcatenation

MySQL CONCAT_WS not working with foreign keys

I'm generating an identifier column in order to group subscriptions. The value of the column uses CONCAT_WS to concatenate different column values from within the table. However, I get an error when trying to alter the table:

Database error code: 1215

Database error: Cannot add foreign key constraint

Here's the query in question. Note that organisation_id and order_id are foreign keys:

ALTER TABLE `subscriptions`
ADD COLUMN `identifier` VARCHAR(191) GENERATED ALWAYS AS (
    CONCAT_WS('-', `organisation_id`, `is_trial`, `start_date`, `end_date`, `order_id`, `superseded_date`)
) STORED;

I tried both CONCAT and CONCAT_WS, as well as removing the foreign keys from the query. There were no errors when I didn't include the foreign keys in the concatenation.

Solution

  • This is a known restriction. The documentation says:

    A foreign key constraint on the base column of a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE or ON DELETE referential actions.

    Your workaround options seem to be:

    • Remove the foreign key constraints.
    • Remove the ON UPDATE and ON DELETE options of the foreign keys.
    • Remove the STORED option of the generated column.