约束基于另一列的一列 [英] Constraint on a column based on another column

问题描述

我有一个表"Table"，其中包含一个ID，Col1和Col2，Col3. Col2可以为0或1. 我希望在Col1具有相同值的行中使用Col2相同. 例

I have a table "Table" that contains an ID, Col1 and Col2, Col3. Col2 can either be 0 or 1. I want Col2 in rows where Col1 has the same value to be the same. Ex

我想要这样的东西

+----+-------+------+-----------+
| ID | Col1  | Col2 |   Col3    |
+----+-------+------+-----------+
|  1 | "One" |    0 | "Yeah"    |
|  2 | "One" |    0 | "Meh"     |
|  3 | "One" |    0 | "Why Not" |
|  4 | "Two" |    1 | "Huh"!    |
+----+-------+------+-----------+

不是

+----+-------+------+-----------+
| ID | Col1  | Col2 |   Col3    |
+----+-------+------+-----------+
|  1 | "One" |    0 | "Yeah"    |
|  2 | "One" |    0 | "Meh"     |
|  3 | "One" |    1 | "Why Not" | (Normally it must be 0 or line 1 and 2 
|  4 | "Two" |    1 | "Huh"!    | Must be "1" )
+----+-------+------+-----------+

推荐答案

即使MySQL支持检查约束，您也不会使用check约束来做到这一点.我认为最好的方法是使用外键约束.

Even if MySQL supported check constraints, you wouldn't do this with check constraints. I think the best way is using foreign key constraints.

您需要另一个具有有效col1/col2值的表:

You need a second table with the valid col1/col2 values:

create table Col1Col2 as (
    col1 varchar(255) not null primary key,
    col2 int not null,
    unique (col1, col2)  -- this is not strictly necessary see below
);

那么您的表格将是:

create table t as (
    id int auto_increment primary key,
    col1 varchar(255) not null,
    col2 int not null
    col3 int,
    constraint fk_t_col1_col2 foreign key (col1, col2) references col1col2(col1, col2)
);

但是，我什至不将col2存储在t中.取而代之的是从t中删除它，然后在col1col2中查找值.

However, I wouldn't even store col2 in t. Instead remove it from t and just look up the value in col1col2.

您的基本问题是您没有以关系格式存储数据，因为此要求表明您有另一个实体.

Your basic problem is that you are not storing the data in a relational format, because this requirement suggests that you have another entity.

这篇关于约束基于另一列的一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！