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);

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);

• mysql
• json
• foreign-keys

1 Answer 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);