QUESTION ABOUT FOREIGN KEY

[Herve Anselme]

Hello,

I use MySQL Workbench 8.0 CE for Windows.

I have a table of parameters : table PARAMS :

ParamTyp ParamNum ParamDescription
Type1 1 Description 1-1
Type1 2 Description 1-2
Type2 1 Description 2-1
Type2 2 Description 2-2
Type2 3 Description 2-3

on this table there is an index (just an ordinary index, not a primary key) on (ParamTyp, ParamNum).

I have another table of activity : table ACTIVITY :

create table ACTIVITY
(
Info1 <type>,
Info2 <type>,
MyTypeNum <same type as ParamNum in PARAMS table>
) ;

Now, what I’d like to do, is :

alter table ACTIVITY add constraint FK_ACTIVITY foreign key (MyTypeNum) references PARAMS(ParamNum) where ParamTyp = 'Type1' ;

in order to make sure that ACTIVITY.MyTypeNum has a value referenced in the « Type1 » parameters ; so that ACTIVITY.MyTypeNum could only have value 1 or 2 ; but not value 3 which does not exist for PARAMS.ParamTyp = 'Type1'.

But when I try, the alter table query makes an error :

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where ParamTyp = 'Type1'' at line 1

Is it because it’s possible, but the syntax I used is wrong (= the alter table query should be written differently) ?
Or is it just impossible to create a foreign key on filtered datas ?

Thanks for your answer and advices.

Regards,
Herve