如何使MySQL约束禁止单行的某些列值？

I have a Table.

我有一张桌子。

word      singular_only      plural_only
homework       1                  0       #homework has no plural
dog            0                  0       #dog and dogs are both real words
scissors       0                  1       #scissor is not a valid singular noun 
 x             1                  1       #should not be insertable

I want to allow the first three rows but not the fourth. How do I add a constraint for this. The values in singular_only and plural_only are boolean. The columns mean whether a word only has a plural form or singular form or both (if both booleans are false). Obviously if something can only be singular it cannot also be plural. What is the best way to model this in MySQL.

我想允许前三行而不是第四行。如何为此添加约束。 singular_only和plural_only中的值是布尔值。列表示单词是仅具有复数形式还是单数形式或两者(如果两个布尔值均为假)。显然,如果某些东西只能是单数,它也不能是复数。在MySQL中对此进行建模的最佳方法是什么?

Is it better to add this constraint in MySQL or to ensure that the overlying code never tries to insert these values.

在MySQL中添加此约束或确保覆盖代码永远不会尝试插入这些值更好。

In other words

换一种说法

singular_only NAND plural_only should also be true

1 个解决方案

#1

You can't add a constraint on one column based on another column except to constrain matching values, which you do not want to do. You should be using a single column to set "singular/plural" status. There are several solutions including using an integer (0,1,2 for the three possible states), an enum, and even another table. I'll leave the choice up to you, but I would favor the flexibility and clarity of another table.

除了约束匹配值(您不想这样做)之外,您不能基于另一列在一列上添加约束。您应该使用单个列来设置“单数/复数”状态。有几种解决方案,包括使用整数(对于三种可能的状态为0,1,2),枚举,甚至是另一个表。我会把选择留给你,但我赞成另一张桌子的灵活性和清晰度。