MYSQL中条件的唯一约束 [英] Unique Constraint with conditions in MYSQL

问题描述

在postgres中，我们定义了一个约束，该约束本质上允许我们将表中具有特定值的条目数限制为1.我们创建了此约束:

In postgres we have a constraint defined that essentially allows us to limit the number of entries in a table with a certain value to one. We created this constraint:

在list_group(visitor_uid)上创建唯一索引list $ default $ uk，其中list_type ='default';

create unique index list$default$uk on list_group(visitor_uid) where list_type = 'default';

这意味着仅当list_type ='default'时才应用唯一约束，因此表中每个访问者只能有一个'default'列表.

Which means that the unique constraint is only applied when the list_type='default' so that there can only be one 'default' list per visitor in the table.

看起来MySql不支持唯一约束上的其他位置.在MySQL的数据库架构中还有另一种方法可以支持这一点吗?

It looks like MySql doesn't support the additional where on the unique constraint. Is there another way to support this in the database schema for MySQL?

推荐答案

MYSQL不支持此类约束.

MYSQL doesn't support such types of constraints.

您应该改用存储过程来插入数据，以便进行一些检查和验证.

You should use stored procudures for inserting data instead, so you can do some checking and validation.

为什么不将默认值定义为必须以1作为主键呢?这样，对pk进行常规的唯一约束就足够了.

Why don't you define your default as such that it must have 1 as primary key? This way a normal unique constraint on the pk would be enough already.

如果没有适合您的内容，您还可以考虑更改数据模型.

If nothing fits to you, you could also consider changing your data model.

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