我在 Postgres 上有這個表模式：
> \d users_types_brands
Table "public.users_types_brands"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------- ----------------------------- ----------- ---------- ------------------------------------------------ --------- -------------- -------------
id | integer | | not null | nextval('users_types_brands_id_seq'::regclass) | plain | |
inserted_at | timestamp without time zone | | | now() | plain | |
updated_at | timestamp without time zone | | | now() | plain | |
users_types_id | bigint | | | | plain | |
brand_id | bigint | | not null | | plain | |
tasks_type_id | integer | | | | plain | |
Indexes:
"users_types_brands_pkey" PRIMARY KEY, btree (id)
"users_types_brands_users_types_id_brand_id_tasks_type_id_index" UNIQUE, btree (users_types_id, brand_id, tasks_type_id)
Foreign-key constraints:
"users_types_brands_users_types_id_fkey" FOREIGN KEY (users_types_id) REFERENCES users_types(id)
Access method: heap
現在該表如下所示：
my_db=# select * from users_types_brands;
id | inserted_at | updated_at | users_types_id | brand_id | tasks_type_id
---- ---------------------------- ---------------------------- ---------------- ---------- ---------------
12 | 2021-10-24 16:43:12.244026 | 2021-10-24 16:43:12.244026 | 2 | 112 | 8
14 | 2021-10-24 17:03:12.012874 | 2021-10-24 17:03:12.012874 | 2 | 111 | 9
(2 rows)
當然，我不能像這樣插入一行：
my_db=# insert into users_types_brands (users_types_id, brand_id, tasks_type_id) values (2, 112, 8);
ERROR: duplicate key value violates unique constraint "users_types_brands_users_types_id_brand_id_tasks_type_id_index"
DETAIL: Key (users_types_id, brand_id, tasks_type_id)=(2, 112, 8) already exists.
但我可以這樣做幾次：
my_db=# insert into users_types_brands (users_types_id, brand_id) values (2, 112);
INSERT 0 1
并獲得這個：
my_db=# select * from users_types_brands;
id | inserted_at | updated_at | users_types_id | brand_id | tasks_type_id
---- ---------------------------- ---------------------------- ---------------- ---------- ---------------
12 | 2021-10-24 16:43:12.244026 | 2021-10-24 16:43:12.244026 | 2 | 112 | 8
14 | 2021-10-24 17:03:12.012874 | 2021-10-24 17:03:12.012874 | 2 | 111 | 9
16 | 2021-10-24 17:15:58.295428 | 2021-10-24 17:15:58.295428 | 2 | 112 |
17 | 2021-10-24 17:16:36.99971 | 2021-10-24 17:16:36.99971 | 2 | 112 |
(4 rows)
現在，根據業務規則， tasks_type_id可以為空??
但是我怎樣才能避免像最后兩行那樣創建重復的行呢？一個 nulltasks_type_id是可以的，但不是兩個或更多。
有沒有人遇到過這種情況？
uj5u.com熱心網友回復：
您可以創建部分唯一索引。這將允許單排與同users_types_id和brand_id和空tasks_type_id，但只有一個。（見演示）
create unique index tasks_type_id_just_1_unique
on users_types_brands (users_types_id, brand_id)
where tasks_type_id is null;
uj5u.com熱心網友回復：
這個問題有兩種基本的解決方案，但都有各自的缺點。
1. 使用部分索引，正如 Belayer 指出的那樣。缺點是對于非空值，您將需要另一個部分索引，因為這將忽略非空值并僅覆寫具有空值的行。
CREATE UNIQUE INDEX "index_for_nulls" ON "table" ( "field_a", "field_b" ) WHERE l"field_c" IS NULL;
CREATE UNIQUE INDEX "index_for_non_nulls" ON "table" ( "field_a", "field_b", "field_c" ) WHERE "field_c" IS NOT NULL;
2.在索引定義中使用 COALESCE 來避免空值。這樣索引將覆寫所有行，但如果您不使用索引中定義的確切陳述句，規劃器將不會使用完整索引
CREATE UNIQUE INDEX "index" ON "table" ( "field_a", "field_b", ( COALESCE( "field_c", -1 ) );
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/337139.html
標籤：sql PostgreSQL 数据库设计
上一篇：如何指定生成的NUMRANGE？