如何为外键添加约束，从而依赖于来自表FK的列的值?

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:

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".

如您所见，Bar应该只接受Foo中的accept id，而Foo_Value是“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.

我尝试添加check约束，但它不接受WHERE语句，因此我无法访问Foo_Value。我试着搜索，但是我真的找不到任何东西，我的解释是错误的。

2 个解决方案

#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

Rextester演示

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

记得在foo上添加另一个触发器更新GOOD -> BAD或delete，它将检查引用完整性。

#2

MySQL does not implement CHECK constraints.

MySQL没有实现检查约束。

You can enforce your rule with a foreign key only if the foreign key includes your Foo_value column. This means you need an extra KEY in your parent table.

只有当外键包含Foo_value列时，才可以使用外键执行规则。这意味着在父表中需要一个额外的键。

ALTER TABLE Foo ADD UNIQUE KEY (Foo_id, Foo_value);

ALTER TABLE Bar ADD COLUMN Foo_value VARCHAR(4) DEFAULT 'GOOD',
    ADD FOREIGN KEY (Foo_id, Foo_value) REFERENCES Foo (Foo_id, Foo_value);

Since you can't use CHECK constraint, you must use triggers to ensure the Bar.Foo_value is 'GOOD':

由于不能使用CHECK约束，所以必须使用触发器来确保Bar。Foo_value是“好”:

CREATE TRIGGER BarInsertFooValueGood BEFORE INSERT ON Bar FOR EACH ROW
    SET Foo_value = 'GOOD';

CREATE TRIGGER BarUpdateFooValueGood BEFORE UPDATE ON Bar FOR EACH ROW
    SET Foo_value = 'GOOD';

#1