Even if MySQL supported check constraints, you wouldn't do this with check
constraints. I think the best way is using foreign key constraints.
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
);
Then your table would be:
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)
);
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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…