MySQL Adding Foreign Key Constraint to Table Using a JSON Expression

Question

I am trying to add an index and a foreign key constraint to an existing table, each of which references a JSON expression.

The ADD INDEX command below is working by itself, but the statement fails when I add the ADD CONSTRAINT.

I'd appreciate any advice regarding what I'm doing wrong. Thanks.

ALTER TABLE MyTable 
ADD INDEX 'MyTable_5' ( ( JSON_VALUE( MyTable, '$.someData' ) )  ),
ADD CONSTRAINT 'MyTable_5FK' FOREIGN KEY ( JSON_VALUE( MyTable, '$.someData' ) )  REFERENCES OtherTable (PK);

Barmar · Accepted Answer · 2025-03-10 20:20:24Z

1

The syntax for ADD INDEX is:

ADD {INDEX | KEY} [index_name]
[index_type] (key_part,...) [index_option] ...

Notice that the syntax inside the parentheses is key_part, and key_part can be either a column name or an expression.

But the syntax for a foreign key is (see documentation):

ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition

Notice that it only allows column names, not expressions. So you can't use a JSON_VALUE() call as a foreign key.

I think you may be able to create a stored generated column using the expression, and then make that a foreign key. However, this isn't allowed to use some of the ON UPDATE and ON DELETE options.

ALTER TABLE MyTable
ADD COLUMN someData INT GENERATED ALWAYS AS (JSON_VALUE(MyTable, '$.someData')) STORED,
ADD CONSTRAINT MyTable_5FK FOREIGN KEY (someData) REFERENCES OtherTable (PK);

answered Mar 10 at 20:20

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Akina Mar 11 at 7:34

If this column is used for FK constraint only then I do not see the reason to make this column stored. The FK condition would be checked only when the row is altered, so the column expression will be calculated anycase. Moreover, MySQL will create a key by this column while creating FK. Hence the column can be created as VIRTUAL. Also, if the column value is not used elsewhere then it is reasonable to make this column invisible.

Akina Mar 11 at 7:42

Oops! I forget that VIRTUAL column cannot be used in FK expression..

Barmar Mar 11 at 15:29

I couldn't find that stated explicitly in the docs, but the documentation on foreign keys only mentions stored generated columns, so I think they're implicitly saying that non-stored columns can't be used at all. This makes sense for efficiency purposes, so it doesn't have to execute the expressions every time the referenced column is changed.

Collectives™ on Stack Overflow

MySQL Adding Foreign Key Constraint to Table Using a JSON Expression

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related