英文:
Unique Constraint group similar values
问题
以下是您要翻译的内容:
假设我有一个数据库,其中包含列 name
、last_name
和 type
。
尽管它们都是字符串,但 type
只能是 TYPE_A
、TYPE_B
或 TYPE_C
。
我希望当 name
和 last_name
已经存在时,数据库中的行应该是“唯一”的。
此外,我希望将 TYPE_A
和 TYPE_B
视为唯一性时的相同类型,并且将 TYPE_C
视为不同类型。基本上,我希望将 TYPE_A
或 TYPE_B
视为 TYPE_A_B
。我不想修改数据库中已经存在的任何值。
我只想将此约束添加到已经存在的数据库中,我该如何做?
(我正在使用 Postgres)
例如:(name, last_name, type)
有效:
John, Doe Type A
John, Doe Type C
无效(唯一性约束违规)
John, Cena Type C
John, Doe Type A
<-
John, Doe Type B
<-
英文:
Let's say I have a database with columns name
, last_name
and type
They're all strings, though type can only be TYPE_A
, TYPE_B
or TYPE_C
I want a row in the database to be Unique
when name
and last_name
already exist.
Additionally, I want TYPE_A
and TYPE_B
to be treated as the same in uniqueness and TYPE
C to be treated differently. Essentially, I want either TYPE_A
or TYPE_B
to be seen as TYPE_A_B
. I don't want to modify any values already present in the database.
I only want to add this constraint to an already existing database, how do I do this?
(I'm using Postgres)
For example: (name, last_name, type)
Valid:
John, Doe Type A
John, Doe Type C
Invalid (Unique constraint violation)
John, Cena Type C
John, Doe Type A
<-
John, Doe Type B
<-
答案1
得分: 2
Postgres的约束不支持表达式,但您可以通过唯一索引实现相同的目标:
create table mytable (name text, last_name text, type text);
create unique index myidx on mytable (
name,
last_name,
( case when type in ('Type A', 'Type B') then 'Type AB' else type end )
);
insert into mytable (name, last_name, type) values ('John', 'Doe', 'Type A');
-- ok
insert into mytable (name, last_name, type) values ('John', 'Doe', 'Type C');
-- ok
insert into mytable (name, last_name, type) values ('John', 'Doe', 'Type B');
ERROR: duplicate key value violates unique constraint "myidx"
DETAIL: Key (name, last_name, (
CASE
WHEN type = ANY (ARRAY['Type A'::text, 'Type B'::text]) THEN 'Type AB'::text
ELSE type
END))=(John, Doe, Type AB) already exists.
如果您只想在类型A/B上强制唯一性,忽略其他类型,那么您可以使用过滤唯一索引:
create unique index myidx
on mytable (name, last_name)
where(type in ('Type A', 'Type B'));
英文:
Postgres' constraints do not support expressions, but you can achieve the same goal with a unique index:
create table mytable (name text, last_name text, type text);
create unique index myidx on mytable (
name,
last_name,
( case when type in ('Type A', 'Type B') then 'Type AB' else type end )
);
insert into mytable (name, last_name, type) values ('John', 'Doe', 'Type A');
-- ok
insert into mytable (name, last_name, type) values ('John', 'Doe', 'Type C');
-- ok
insert into mytable (name, last_name, type) values ('John', 'Doe', 'Type B');
ERROR: duplicate key value violates unique constraint "myidx"
DETAIL: Key (name, last_name, (
CASE
WHEN type = ANY (ARRAY['Type A'::text, 'Type B'::text]) THEN 'Type AB'::text
ELSE type
END))=(John, Doe, Type AB) already exists.
If you wanted to enforce unicity on types A/B only, ignoring other types, then you would use a filtering unique index instead:
create unique index myidx
on mytable (name, last_name)
where(type in ('Type A', 'Type B'));
答案2
得分: 2
创建一个功能性的唯一索引:
create unique index idx on mytable
(
name,
last_name,
(case when type in ('Type A', 'Type B') then 'TYPE_A_B' else type end)
);
(在这里,额外的括号在 CASE
表达式周围是必需的。)
演示:https://dbfiddle.uk/rIzYyU-n
英文:
Create a functional unique index:
create unique index idx on mytable
(
name,
last_name,
(case when type in ('Type A', 'Type B') then 'TYPE_A_B' else type end)
);
(The additional parentheses around the CASE
expression are mandatory here.)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论