唯一约束组相似的值

huangapple go评论72阅读模式
英文:

Unique Constraint group similar values

问题

以下是您要翻译的内容:

假设我有一个数据库,其中包含列 namelast_nametype

尽管它们都是字符串,但 type 只能是 TYPE_ATYPE_BTYPE_C

我希望当 namelast_name 已经存在时,数据库中的行应该是“唯一”的。

此外,我希望将 TYPE_ATYPE_B 视为唯一性时的相同类型,并且将 TYPE_C 视为不同类型。基本上,我希望将 TYPE_ATYPE_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 )
);

fiddle

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 (&#39;Type A&#39;, &#39;Type B&#39;) then &#39;Type AB&#39; else type end )
);

fiddle

insert into mytable (name, last_name, type) values (&#39;John&#39;, &#39;Doe&#39;, &#39;Type A&#39;);
-- ok

insert into mytable (name, last_name, type) values (&#39;John&#39;, &#39;Doe&#39;, &#39;Type C&#39;);
-- ok

insert into mytable (name, last_name, type) values (&#39;John&#39;, &#39;Doe&#39;, &#39;Type B&#39;);
ERROR:  duplicate key value violates unique constraint &quot;myidx&quot;
DETAIL:  Key (name, last_name, (
CASE
    WHEN type = ANY (ARRAY[&#39;Type A&#39;::text, &#39;Type B&#39;::text]) THEN &#39;Type AB&#39;::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 (&#39;Type A&#39;, &#39;Type B&#39;));

答案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 (&#39;Type A&#39;, &#39;Type B&#39;) then &#39;TYPE_A_B&#39; else type end)
);

(The additional parentheses around the CASE expression are mandatory here.)

Demo: https://dbfiddle.uk/rIzYyU-n

huangapple
  • 本文由 发表于 2023年5月25日 16:04:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/76330117.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定