How to add constraint for foreign key so it depends on value of column from table FK is coming from?

Question

I need to set constraint so that table accepts foreign keys from other table only if another column in that table has specific value.

Database is quite complex but let me give example:

Table Foo:

Foo_id | Foo_value | Another values...
   1   |  "GOOD"   |
   2   |  "BAD"    |
   3   |  "GOOD"   |

Table Bar:

Bar_id | Foo_id(FK) | Another values...
   1   |     1      |
   2   |     1      |
   3   |     3      |

As you can see Bar should only accept accept id from Foo where Foo_Value is "GOOD".

I tried adding check constraint but it doesn't accept WHERE statement so I can't access Foo_Value. I tried searching but I can't really find anything or my explanation of this is wrong.

Answer 1

Possibly the only way is to use trigger (for insert/update):

create trigger chk_bar before insert on bar  /* before update */
for each row 
begin  
   if  not exists (SELECT 1 FROM foo 
                  WHERE Foo_value = 'GOOD' AND foo_id = new.foo_id)  then
       SIGNAL SQLSTATE '45000'   
       SET MESSAGE_TEXT = 'Invalid FK';
   end if; 
end; 

Rextester Demo

Remember to add another trigger on foo on update GOOD -> BAD or delete that will check referential integrity.